How to constrain a table linking two tables in many-to-many relationship

Say I have the following tables and columns:

comp: 
    id, 
    model

dvd: 
    id, 
    model

comp2dvd: 
    id, 
    id_comp, 
    id_dvd

A computer can have multiple dvd drives, even of the same model, and a dvd drive can appear in multiple computers. How do I make it so that comp2dvd table can have only existing 开发者_高级运维comp and dvd ids?

Thanks

you cannot normally have a foreign key reference without an enforcing index which identifies a column or combination of columns as unique (such as, but not limited to, a primary key)

comp should have id as primary key

dvd should have id as primary key

comp2dvd should have id as primary key

comp2dvd should have id_comp as foreign key references(comp.id)

comp2dvd should have id_dvd as foreign key references(dvd.id)

DO NOT let comp2dvd have a unique index or constraint on the pair of columns (id_comp, id_dvd), since you need duplicates for computers with multiple identical drives

Why not just go with a straight join table? You could add a constraint on the join table to only allow the use of one DVDID + computer combination.

Computer  -----> CompDVD  <------ DVD --------> Model
ID               CompID           ID            ID
                 DVDID

By adding foreign keys to id_comp and id_dvd?