MySQL MySQL中的多列外键？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/953035/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Multiple-column foreign key in MySQL?
提问by James Skidmore
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?
我有一个表，其主键由两列（product_id、attribute_id）组成。我有另一个表需要引用这个表。如何在另一个表中创建外键以将其链接到具有两个主键的表中的行？
回答by PatrikAkerstrand
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)
- MySQL 需要索引外键，因此引用列上的索引
- 使用约束语法使您能够命名约束，以便在需要时更轻松地在以后更改和删除。
- InnoDB 强制执行外键，MyISAM 没有。（语法被解析但被忽略）
回答by GSerg
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.
一张表上只能有一个主键。事实上 in 可以包含多个字段并不会增加主键的数量，仍然有一个。
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).
由于 PK 对的一部分不是唯一的，因此您显然必须创建一个也引用两个字段的外键：REFERENCES t1 (f1, f2)。
回答by zloctb
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)
