How to Create a Foreign Key Referencing a Multi-Column Primary Key in MySQL?

Referencing a Multi-Column Primary Key in MySQL

In a relational database scenario where a table possesses a composite primary key defined by multiple columns (e.g., product_id, attribute_id), a common question arises: how can another table establish a foreign key relationship with this multi-column primary key?

Solution:

Composite Foreign Key Syntax:

The solution involves defining a composite foreign key in the referencing table. The syntax for such foreign keys is as follows:

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

• refcol1 and refcol2: Columns in the referencing table that form the foreign key composite key.
• col1 and col2: Columns in the referenced table that make up the primary key.
• fk_mrt_ot: A constraint name for this foreign key (optional but recommended).

Key Considerations:

• Indexing: MySQL requires foreign key columns to be indexed, ensuring efficient join operations.
• CONSTRAINT Syntax: Utilizing the constraint syntax provides a named reference to the foreign key, making subsequent alterations or removal easier.
• Engine: InnoDB is a transactional engine that enforces foreign key constraints, but MyISAM (a non-transactional engine) ignores foreign key specifications.

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