| Author |
 Topic  |
|
|
MIK_2008
Aged Yak Warrior
Pakistan
827 Posts |
 Posted - 02/28/2013 : 05:40:36
|
Hello team,
Need help on defining one to one constraint on columns that are in same table. For example
Create Table tblTest (col1 int not null, Col2 int Not Null, StartDate smalldatetime EndDate smalldatetime)
I need to implement a constraint such that there is one to one relationship between col1 and col2, for a given start and end date. Some more explanation would be that there would be a bulk data load between start and end date and I want to ensure that in that specific period Col1 and Col2 data should have one to one relation. And then in second data load (which will have different StartDate and EndDate) it can be repeated but still should be having one to one relationship.
Appreciate all yours help. Thanks!
Cheers
MIK |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/28/2013 : 06:16:01
|
Can you illustrate it by means of sample data? do you mean for startdate,enddate value combination there cant be more than one col2 for same col1? i would have done it by means of trigger/ udf based check constraint
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
MIK_2008
Aged Yak Warrior
Pakistan
827 Posts |
Posted - 02/28/2013 : 06:37:10
|
Thanks Visakh, Cant this be implemented using any sort of filtered index?
Sample data would be
Col1, Col2, StartDate, EndDate
1,1,2013-02-01,2013-02-28 --Valid Record
1,2,2013-02-01,2013-02-28 --Not a Valid Record
2,2,2013-02-01,2013-02-28 --Valid Record
2,1,2013-02-01,2013-02-28 --Not a Valid Record
2,1,2013-03-01,2013-03-31 --Valid Record
...
..
Note the row 4 and 5; as Start and End dates are changed so the 2,1 (col1,col2) combination gets valid, which is already added but with a different start and end date. So you can say rows 1-4 are fist data load cycle and 5 onward is a second one.
Let me know if you need any further explanation. Thanks again
Cheers
MIK |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/28/2013 : 10:11:59
|
set two unique constraints one on Col1,StartDate,EndDate and other on Col2,StartDate,EndDate combinations
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
defining data integrity constraint
