Menu
I have a table with columns a b and c, and if c is false then I only want to allow insertions if columns a and b are unique, but if c is true then a and b do not need to be unique.
Example: There can only be one (foo, bar, false) in the table, but no limit on how many (foo, bar, true) there can be.
I tried something like CONSTRAINT blah UNIQUE (a,b) AND CHECK (C is TRUE) but I can't figure out the correct syntax.
902
You can apply multiple CHECK constraints to a single column. You can also apply a single CHECK constraint to multiple columns by creating it at the table level.
You can have multiple check constraint for a single column. If data being inserted or updated violates a check constraint the database engine will not allow the INSERT or UPDATE operation to occur. A check constraint consists of a logical expression to identify what is a valid expression.
A PRIMARY KEY constraint automatically has a UNIQUE constraint. However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
The syntax for creating a unique constraint using an ALTER TABLE statement in SQL Server is: ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);
Create an indexed view returning a and b with a filter WHERE C = false, then create a unique index on the view. This is a general approach
If you have SQL Server 2008, then create a unique filtered index instead
Stored procedure
Trigger (before or after)
142
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
