How to Reference a Two-Column Primary Key with Foreign Keys in MySQL?

Referencing a Two-Column Primary Key in MySQL with Foreign Keys

When designing relational database tables, it's common to encounter situations where multiple columns form the primary key of a table, and another table needs to reference it. In MySQL, creating a foreign key to link to such a compound primary key requires some specific considerations.

To establish a foreign key in another table that references the two-column primary key (product_id, attribute_id):

CREATE TABLE MyReferencingTable (
    [COLUMN DEFINITIONS]
    refcol1 INT NOT NULL,
    refcol2 INT NOT NULL,
    CONSTRAINT fk_mrt_ot FOREIGN KEY (refcol1, refcol2)
    REFERENCES OtherTable(product_id, attribute_id)
) ENGINE=InnoDB;

This syntax dictates several key points:

• Indexing Referencing Columns: MySQL requires foreign key columns to be indexed, so the referencing columns (refcol1, refcol2) must have indexes.
• Using Constraint Syntax: The CONSTRAINT syntax allows you to name the constraint, making it easier to manage and manipulate later if necessary.
• Using InnoDB Engine: InnoDB strictly enforces foreign keys, ensuring data integrity. MyISAM, on the other hand, parses the foreign key constraints but ultimately ignores them.

The above is the detailed content of How to Reference a Two-Column Primary Key with Foreign Keys in MySQL?. For more information, please follow other related articles on the PHP Chinese website!