Column Combination Constraint

Question

Column Combination Constraint

ScorpionJL

SSC Enthusiast

Points: 102
More actions
May 2, 2006 at 3:04 pm

#90045

I have a table with similar to the following (in SQL2000)
Table: Col1 (int), Col2(int)
Primary Key (Col1, Col2)
Unique Key (Col2, Col1)
Unfortunatly, I need the data in the table to be unique for the combination of columns as well. If the table contained the data
1, 1
1, 2
3, 2
I want to allow the following to get inserted
1, 3 or 2, 2
but I need to prevent the following from getting inserted
2, 1(because 1,2 already exists) or
2, 3(because 3,2 already exists) etc.
Is this possible with a table constraint or will this have to be done with a trigger? If through a trigger, will a standard trigger be fine (I cannot guarantee the insert/update will be done during a transaction) or will it have to be done via an INSTEAD OF trigger?
Thanks in advance for any advise.

Viewing 4 posts - 1 through 4 (of 4 total)

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

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 1

You'd probably need to use a check constraint on something like that. But because you can't use queries inside check constraints it wouldn't work. I'd think you'll need to use a trigger to force this.

CREATE TRIGGER tr_table_ins_upd ON table

FOR INSERT, UPDATE

AS

BEGIN

IF EXISTS (SELECT 1 FROM table t JOIN inserted i ON t.col1 = i.col2 AND t.col2 = i.col1)

BEGIN

RAISERROR('You can''t do that.',16,1)

ROLLBACK TRANSACTION

END

There's an implied transaction when the insert or update is done, regardless of if the rest of your code is explicitly begin/ending a transaction.

Lowell SSC Guru Points: 323546 More actions · Answer 2

could you add a check constraint that says column2 must be less than or equal to column 1?

that would prevent 3,2 or 2,1 from inserting.

that way you enforce the order to be a certain way, and the unique constraint handles the rest?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ScorpionJL SSC Enthusiast Points: 102 More actions · Answer 3

I figured I would have to go with the trigger but I just wanted to make sure I wasn't missing something.

The constraint will not really work in my situation because I simplified the question. In reality I have several columns that I will have to deal with. If it was only two columns then the constraint idea would work just fine.

Thanks for the help.