How Do I Reference a Multi-Column Primary Key in MySQL?

Referencing a Multi-Column Primary Key in MySQL

When establishing relationships between tables, it's common to encounter scenarios where a primary key comprises multiple columns. In MySQL, creating a foreign key to such a table requires a tailored approach.

Consider a scenario where you have a table with the following primary key:

CREATE TABLE ProductAttributes (
  product_id INT NOT NULL,
  attribute_id INT NOT NULL,
  PRIMARY KEY (product_id, attribute_id)
);

Now, suppose you have another table that needs to reference the ProductAttributes table. To establish a foreign key that links the two tables, follow these steps:

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

By specifying the columns in the referencing table and the corresponding columns in the primary key table, you create a foreign key constraint. This constraint ensures that each row in the referencing table has a matching primary key in the primary key table.

Considerations:

• MySQL requires foreign keys to be indexed, which explains the index on the referencing columns.
• Utilize the constraint syntax to name the constraint for easier alteration or deletion later on.
• Remember that InnoDB enforces foreign keys, while MyISAM ignores them.

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