Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Question re Check Contraints Expand / Collapse
Author
Message
Posted Monday, May 12, 2014 4:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 12, 2014 7:53 AM
Points: 3, Visits: 5
Hi all,
I am creating a database where each record is required to have a twin record in the database.

These is a type a value and a type b value and both must be present for the record to be valid.

-------------------------------
--Orders---------------------
-------------------------------
Customer_ID, Order_Type, Product_Code
54, a, 00345
54, b, 00356


Is this something that would have to be done programmatically, or is it possible to create a constraint of some sort to ensure this?

I would appreciate any advice.

Paul
Post #1569765
Posted Monday, May 12, 2014 5:44 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, August 29, 2014 8:23 AM
Points: 326, Visits: 729
YOu should be able to do it by creating a VIEW ...WITH CHECK as described here:

[url=http://www.sqlservercentral.com/Forums/Topic950355-374-1.aspx][/url]
Post #1569787
Posted Monday, May 12, 2014 6:13 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 7:55 AM
Points: 15,662, Visits: 28,055
I think I would probably do this programmatically. I mean, if you tried using a trigger that stopped inserts of an A type row unless there was already a B type row, how do you ensure that the B type row only gets inserted if there's an A type row? Just have the stored procedure that creates these values always create both.

Of course, then the question comes up, why are you storing two rows for something like this?


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1569803
Posted Monday, May 12, 2014 6:18 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:17 AM
Points: 1,778, Visits: 4,655
paul 31908 (5/12/2014)
Hi all,
I am creating a database where each record is required to have a twin record in the database.

These is a type a value and a type b value and both must be present for the record to be valid.


Quick question, at what point should the validation be done?
Post #1569805
Posted Monday, May 12, 2014 6:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 12, 2014 7:53 AM
Points: 3, Visits: 5
Hi,

Both rows will be entered at the same time. This is part of a web ordering system, whereby a customer is required to order two items from the same product inventory, but for two slightly different purposes, hence type a and type b. It is important that

1) They order two items (these can be the same product_code)
2) We need to be able to distinguish which is Type A and which is Type B

The website will enter both records in a single transaction, so I was hoping to have the check performed after the insert.

Paul
Post #1569817
Posted Monday, May 12, 2014 7:24 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 7:55 AM
Points: 15,662, Visits: 28,055
For the test to past, both records have to be there, so you can't really automate it from the database end since either record could be missing at the moment of an insert unless you only check for A from B or B from A, but never the other way around. That's why I lean towards having an external check.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1569838
Posted Monday, May 12, 2014 7:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 12, 2014 7:53 AM
Points: 3, Visits: 5
Is that true even when both records are inserted within a single transaction?
Post #1569841
Posted Monday, May 12, 2014 7:51 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 7:55 AM
Points: 15,662, Visits: 28,055
I'd say, test it out and see how it works. The inserted table is available from a trigger. As long as you designate it as an AFTER trigger... test it.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1569853
Posted Monday, May 12, 2014 11:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:13 AM
Points: 2,044, Visits: 3,060
You can't use a standard CHECK constraint, as all values must be available on a single row for that.

You could easily use an AFTER DELETE, INSERT[, UPDATE ] trigger if you wanted to.


SQL DBA,SQL Server MVP('07, '08, '09)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1569997
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse