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:
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.
"What I hear, I forget; What I see, I remember; What I do, I understand." --Confucius
Remeber to Mark as Answer if this post helped you.
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.
allanhorwitz
Contributor
2517 Points
623 Posts
Special Insert Constraint Rule for Two Columns
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
grundebar
Contributor
4515 Points
726 Posts
Re: Special Insert Constraint Rule for Two Columns
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.
Remeber to Mark as Answer if this post helped you.
TabAlleman
All-Star
15575 Points
2702 Posts
Re: Special Insert Constraint Rule for Two Columns
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.
so
CREATE UNIQUE INDEX(or CONSTRAINT) SomeNameForMyConstraint
ON MyTable(Col2, Col3)