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 |
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
 Posted - 2013-02-28 : 05:40:36
|
| Hello team,Need help on defining one to one constraint on columns that are in same table. For exampleCreate 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!CheersMIK |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-28 : 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 MVPhttp://visakhm.blogspot.com/ |
 |
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-02-28 : 06:37:10
|
| Thanks Visakh, Cant this be implemented using any sort of filtered index? Sample data would be Col1, Col2, StartDate, EndDate1,1,2013-02-01,2013-02-28 --Valid Record1,2,2013-02-01,2013-02-28 --Not a Valid Record2,2,2013-02-01,2013-02-28 --Valid Record2,1,2013-02-01,2013-02-28 --Not a Valid Record2,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 againCheersMIK |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-28 : 10:11:59
|
| set two unique constraints one on Col1,StartDate,EndDate and other on Col2,StartDate,EndDate combinations------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
defining data integrity constraint