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?
have you tried setting both columns as unique?
ALTER TABLE LinkTable
ADD CONSTRAINT LinkTable_Unique UNIQUE (TableA_id, TableB_id);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With