Menu
I'd like to define a constraint between two nullable FK's in a table where if one is null the other needs a value, but both can't be null and both can't have values. Logic is the derived table inherits data from the either of the FK tables to determine its type. Also, for fun bonus points, is this a bad idea?
795
To enforce NOT NULL for a column in SQL Server, use the ALTER TABLE .. ALTER COLUMN command and restate the column definition, adding the NOT NULL attribute.
ADD CONSTRAINT is a SQL command that is used together with ALTER TABLE to add constraints (such as a primary key or foreign key) to an existing table in a SQL database. The basic syntax of ADD CONSTRAINT is: ALTER TABLE table_name ADD CONSTRAINT PRIMARY KEY (col1, col2);
You can add a CHECK constraint to ensure that only 'Y' or 'N' is allowed, though; that is probably what you are seeking. Subject to variations per DBMS (now identified as Oracle), you could write something like: ALTER TABLE STOREREPS MODIFY (COMM DEFAULT 'Y'), ADD CONSTRAINT check_comm_y_n CHECK (COMM IN ('Y', 'N'));
One way to achieve it is to simply write down what "exclusive OR" actually means:
CHECK (
(FK1 IS NOT NULL AND FK2 IS NULL)
OR (FK1 IS NULL AND FK2 IS NOT NULL)
)
However, if you have many FKs, the above method can quickly become unwieldy, in which case you can do something like this:
CHECK (
1 = (
(CASE WHEN FK1 IS NULL THEN 0 ELSE 1 END)
+ (CASE WHEN FK2 IS NULL THEN 0 ELSE 1 END)
+ (CASE WHEN FK3 IS NULL THEN 0 ELSE 1 END)
+ (CASE WHEN FK4 IS NULL THEN 0 ELSE 1 END)
...
)
)
BTW, there are legitimate uses for that pattern, for example this one (albeit not applicable to MS SQL Server due to the lack of deferred constraints). Whether it is legitimate in your particular case, I can't judge based on the information you provided so far.
129
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
