Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
_sqllearner
Starting Member
9 Posts |
 Posted - 2007-04-30 : 17:58:09
|
| What is the best way to establish constraints between two columns in a tableFor example (ID1, Date is the primary Key, ID2 can be null sometimes)ID1 EffDate ExpDate ID2 ID2_Location1 1/1/2007 12/31/9999 122 ABC2 1/1/2007 03/31/2007 124 XYZ2 4/1/2007 12/31/9999 124 XYZ13 1/1/2007 12/31/9999 <Null> <Null>I would like to establish a constraint that extablishes one to one relation between ID1 and ID2. Meaning in the above example ID2 =122 should not be assingned to any ID1 other than 1.(For example, I should not be able to insert another row like ID1 EffDate ExpDate ID2 ID2_Location4 1/1/2007 12/31/9999 122 ABC)Beacause this table is maintained in a manual way, sometimes the ID2 which has already been assigned an ID1 is being assigned to another ID1.What kind of constraint or rule will avoid this scenerio.ThanksRaj |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-04-30 : 18:46:38
|
| You can do this with a unique constraint on column ID2, but that would mean that you could only have one row where ID2 was null.If you need to have multiple rows where ID2 is null, you can create a view on the table that only includes rows where ID2 is not null, and add a unique constraint on ID2. There are technical issues with indexed views that may make this not desirable; you can read about indexed views in SQL Server Books Online.You could also move the ID2 column to a separate table to relate ID1 and ID2 and add a unique constraint on both ID1 and ID2 with a FK relationship to the ID1 column in the current table.CODO ERGO SUM |
 |
|
|
|
|
|
Check Constraints