SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 defining data integrity constraint
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1050 Posts

Posted - 02/28/2013 :  05:40:36  Show Profile  Reply with Quote
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
52249 Posts

Posted - 02/28/2013 :  06:16:01  Show Profile  Reply with Quote
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/

Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1050 Posts

Posted - 02/28/2013 :  06:37:10  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/28/2013 :  10:11:59  Show Profile  Reply with Quote
set two unique constraints one on Col1,StartDate,EndDate and other on Col2,StartDate,EndDate combinations

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.41 seconds. Powered By: Snitz Forums 2000