首页 文章

MySQL中的多列外键?

提问于
浏览
45

我有一个表,其主键由两列(product_id,attribute_id)组成 . 我有另一个表需要引用此表 . 如何在另一个表中创建外键以使用两个主键将其链接到表中的行?

3 回答

  • 75

    像这样的东西应该这样做:

    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要求对外键进行索引,因此引用列上的索引

    • 使用约束语法可以命名约束,如果需要,可以更容易地在以后更改和删除 .

    • InnoDB强制执行外键,MyISAM不执行 . (语法被解析但被忽略)

  • 3

    表上只能有一个主键 . 事实上可以包含多个字段不会增加主键的数量,还有一个 .

    由于PK对的一部分不是唯一的,因此您显然必须创建一个引用两个字段的外键:REFERENCES t1(f1,f2) .

  • 2

    如果我们想要外键的逻辑像这样

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

    例:

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

    选择:

    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)
    

相关问题