Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PSQL unique constraint on two columns

I have a table which I use to link two more tables together in a 1 to many relationship.

TableA
id
name

TableB
id
name

LinkTable
TableA_id
TableB_id

Basically, one of TableA can have many of TableB. Very simple. The problem I have now is to create a constraint that follows the rules of this relation ship as such:

LinkTable
TableA_id    TableB_id
1            1
1            2
1            3
2            1
2            2
2            3

I want to create a unique constraint which combines both the columns together as the unique value. So in the link table above, with this new constraint, I can

INSERT INTO LinkTable (TableA_id, TableB_id) VALUES (1, 4);
INSERT INTO LinkTable (TableA_id, TableB_id) VALUES (1, 5);
INSERT INTO LinkTable (TableA_id, TableB_id) VALUES (2, 6);
INSERT INTO LinkTable (TableA_id, TableB_id) VALUES (3, 1);

With out any problems

And if I try to insert:

INSERT INTO LinkTable (TableA_id, TableB_id) VALUES (1, 1);
INSERT INTO LinkTable (TableA_id, TableB_id) VALUES (1, 3);

The constraint will fire because there is already a row with 1,1 and 1,3. How would I create a postgres constraint to do this? If i set a unique constraint to both rows, then I cannot have more then one TableA_id the same and more then one TableB_id the same.

What is the solution?

like image 439
Ozzy Avatar asked Dec 02 '22 20:12

Ozzy


1 Answers

have you tried setting both columns as unique?

ALTER TABLE LinkTable 
ADD CONSTRAINT LinkTable_Unique UNIQUE (TableA_id, TableB_id);
like image 56
John Woo Avatar answered Dec 15 '22 17:12

John Woo