Question re Check Contraints

Question

Post reply

Question re Check Contraints

paul 31908

SSC Enthusiast

Points: 136
More actions
May 12, 2014 at 4:54 am

#291145

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

gbritton1 SSCertifiable Points: 6522 More actions · Answer 1

YOu should be able to do it by creating a VIEW ...WITH CHECK as described here:

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 2

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

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?

😎

paul 31908 SSC Enthusiast Points: 136 More actions · Answer 4

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

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 5

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

paul 31908 SSC Enthusiast Points: 136 More actions · Answer 6

Is that true even when both records are inserted within a single transaction?

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 7

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ScottPletcher SSC Guru Points: 100948 More actions · Answer 8

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) A socialist is someone who will give you the shirt off *someone else's* back.