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?

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)

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).

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)