how to keep combination of cells unique

Question

i have table A and table B. I have a bridge table called tableC

in table C i have:

ID
tableA_ID
tableB_ID

ID is the primary key.

i also want to enforce the combination of tableA_ID and tableB_ID to be unique so there are no duplicate records.

how do i enforce this?

Answer 1

Drop the ID column then make the other two columns the primary key and their uniqueness will be enforced by the database server. It's not really necessary to have the ID column - even though it serves as a handy way of referencing a particular record - as the uniqueness of the other two columns will mean that they are sufficient to reference a particular record.

You may also want to put an index on this table, that includes bothe columns, to make access faster.

Answer 2

Make the PRIMARY KEY tableA_ID and tableB_ID, EXCLUDING ID

lets say we have a table TABLEA with values

tableAID
1
2
3

and table TABLEB with values

tableBID
4
5
6

making the primary key (ID, tableA_ID, tableB_ID) will not work eg.

ID | tableAID | tableBID
1 | 1 | 4
2 | 1 | 4

will work fine with the above pk, but you need PRIMARY KEY (tableA_ID, tableB_ID)

Answer 3

create unique index myIdx on tableC(tableA_ID, tableB_ID)

or whatever the syntax for your particular database system is.