Multiple-column foreign key in MySQL?

Question

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?

Chor

Answer 1

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)

Supporting

FWIW, MyISAM does parse and ignore foreign key syntax. And you don't need to declare the index redundantly since MySQL 4.1.2. – Quinta

Also make sure that both tables are InnoDB since, as Bill points out, MyISAM does not support foreign keys. – Vide

I was voted down twice due to the explicit index? Harsh. I did point out that InnoDB was neccessary. – Supporting

You say - MySQL requires foreign keys to be indexed, hence the index on the referencing columns - shouldn't that be REFERENCED COLUMNS? – Planer

Answer 2

There can only be one primary key on a table. The fact in can consist of more than one field does not increase number of primary keys, there's still one.

Since a part of the PK pair is not unique, you obviously have to create a foreign key that refers to two fields as well: REFERENCES t1 (f1, f2).

Giorgione

Answer 3

If we want logic for foreign key some like this

FOREIGN KEY COmments(issue_id)
REFERENCES Bugs(issue_id) OR FeatureRequests(issue_id)

Example:

CREATE TABLE Issues (
issue_id int PRIMARY KEY,
status VARCHAR(20)

);




CREATE TABLE Comments (
comment_id int PRIMARY KEY,
issue_type VARCHAR(20), -- "Bugs" or "FeatureRequests"
issue_id BIGINT UNSIGNED NOT NULL,
comment TEXT
);



CREATE TABLE Bugs (
issue_id int PRIMARY KEY,
severity VARCHAR(20),
FOREIGN KEY (issue_id) REFERENCES Issues(issue_id)
);
CREATE TABLE FeatureRequests (
issue_id int PRIMARY KEY,
sponsor VARCHAR(50),
FOREIGN KEY (issue_id) REFERENCES Issues(issue_id)
);





INSERT INTO Issues VALUES(1,'ON'),(2,'ON'),(3,'OFF'),(6,'OFF'),(8,'ON');

INSERT INTO Comments VALUES(1,'Bugs',1,'A'),(2,'Bugs',3,'B'),(3,'Bugs',1,'C'),(4,'Bugs',3,'D'),(5 ,'FeatureRequests',8,'L'),
(6,'FeatureRequests',6,'W'),(7,'FeatureRequests',1,'ZX');



INSERT INTO Bugs VALUES(1,'severity_1'),(3,'severity_for_3');


INSERT INTO FeatureRequests VALUES(2,'sponsor_2_'),(8,'sponsor_for_8'),(1,'sponsor_for_1')

SELECTS :

MariaDB [test]> SELECT * FROM Comments JOIN FeatureRequests  ON Comments.issue_i
d = FeatureRequests.issue_id AND Comments.issue_type= 'FeatureRequests';


MariaDB [test]> SELECT * FROM Comments JOIN Bugs  ON Comments.issue_id = Bugs.is
sue_id AND Comments.issue_type= 'Bugs';
+------------+------------+----------+---------+----------+----------------+
| comment_id | issue_type | issue_id | comment | issue_id | severity       |
+------------+------------+----------+---------+----------+----------------+
|          1 | Bugs       |        1 | A       |        1 | severity_1     |
|          2 | Bugs       |        3 | B       |        3 | severity_for_3 |
|          3 | Bugs       |        1 | C       |        1 | severity_1     |
|          4 | Bugs       |        3 | D       |        3 | severity_for_3 |
+------------+------------+----------+---------+----------+----------------+
4 rows in set (0.00 sec)

Loralyn