How to Create Foreign Keys Referencing Composite Primary Keys in MySQL?

Creating Foreign Keys that Reference Composite Primary Keys in MySQL

MySQL allows for the creation of foreign keys that reference composite primary keys. This can be useful when you have a table with a primary key consisting of multiple columns and need to establish a relationship between it and another table.

How to Implement:

To create a foreign key that references a composite primary key, you need to define the foreign key columns and specify the referenced primary key columns using the FOREIGN KEY constraint. Here's an example:

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;

Key Points:

• MySQL requires foreign key columns to be indexed, so ensure that the referencing columns have an index.
• Using CONSTRAINT fk_nm syntax allows you to name the constraint, making it easier to manage.
• By using the InnoDB storage engine, you enforce foreign key constraints (MyISAM ignores them).

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