Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Oct 19, 2010 08:24 PM by TabAlleman
Oct 19, 2010 07:57 PM|LINK
I have a SQL Server 2000 table with 7 columns and would like to add a constraint that prevents a row being added with the same value for both Col2 and Col3.
So, for example, if Col2 = 205 and Col3 = 5, then the constraint should prevent inserting another row with the values for Col2 = 205 and Col3 = 5.
The following is an example of what I would like to prevent:
Col1 Col2 Col3 Col4 Col5 Col6
1 205 5 1 1 1
2 205 5 0 0 0
Oct 19, 2010 08:12 PM|LINK
Constraints cannot view data outside of the current row. You will need to use a table trigger instead. In a SQL Server trigger there are two special tables available: Inserted and Deleted. You woul look into the Inserted table for any disallowed combination
and then Rollback the transaction if you encounter one.
Oct 19, 2010 08:24 PM|LINK
Actually, you can put a UNIQUE CONSTRAINT or UNIQUE INDEX on your table on both columns and it will enforce that the combination of those two columns is unique across all rows in your table.
CREATE UNIQUE INDEX(or CONSTRAINT) SomeNameForMyConstraint
ON MyTable(Col2, Col3)