I have a table that has a primary key consisting of two columns (product_id, attribute_id). I have another table that needs to reference this table. How can I make a foreign key in the other table to link it to a row in the table with two primary keys?
解决方案
Something like this ought to do it:
CREATE TABLE MyReferencingTable AS (
[COLUMN DEFINITIONS]
refcol1 INT NOT NULL,
rofcol2 INT NOT NULL,
CONSTRAINT fk_mrt_ot FOREIGN KEY (refcol1, refcol2)
REFERENCES OtherTable(col1, col2)
) ENGINE=InnoDB;
MySQL requires foreign keys to be indexed, hence the index on the referencing columns
Use of the constraint syntax enables you to name a constraint, making it easier to alter and drop at a later time if needed.
InnoDB enforces foreign keys, MyISAM does not. (The syntax is parsed but ignored)