在多列上添加外键

发布于 2024-10-17 18:34:39 字数 482 浏览 7 评论 0原文

我正在尝试在表的两列上创建外键以指向另一个表的同一列，但我似乎收到错误...

这就是我所做的：

CREATE TABLE test2 (
  ID INT NOT NULL AUTO_INCREMENT,  
  col1 INT NOT NULL,
  col2 INT NOT NULL, 
  PRIMARY KEY (ID),
  CONSTRAINT fk FOREIGN KEY (col1, col2)
                REFERENCES test1(ID, ID)
  ON UPDATE CASCADE
  ON DELETE RESTRICT
) ENGINE=InnoDB;

但我收到

错误 1005 (HY000 ): 无法创建表 'DB.test2' (errno: 150)

但是，如果我只有一列，则表会正确创建。

有人可以向我指出错误在哪里吗？

谢谢 n

原文

I'm trying to create a foreign key on two columns of a table to point to the same column of another table, but I seem to get an error...

Here's what I do:

CREATE TABLE test2 (
  ID INT NOT NULL AUTO_INCREMENT,  
  col1 INT NOT NULL,
  col2 INT NOT NULL, 
  PRIMARY KEY (ID),
  CONSTRAINT fk FOREIGN KEY (col1, col2)
                REFERENCES test1(ID, ID)
  ON UPDATE CASCADE
  ON DELETE RESTRICT
) ENGINE=InnoDB;

But I get

ERROR 1005 (HY000): Can't create table 'DB.test2' (errno: 150)

If I only have one column, however, the table is correctly created.

Could someone point out to me where the error is?

Thanks
n

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

扬花落满肩 2024-10-24 18:34:39

在这里尝试了一下，得到了同样的错误。但这是有效的：

CREATE TABLE test2 (
  ID INT NOT NULL AUTO_INCREMENT,  
  col1 INT NOT NULL,
  col2 INT NOT NULL, 
  PRIMARY KEY (ID),
  CONSTRAINT fk FOREIGN KEY (col1)
                REFERENCES test1(ID)
  ON UPDATE CASCADE
  ON DELETE RESTRICT,
  CONSTRAINT fk2 FOREIGN KEY (col2)
                REFERENCES test1(ID)
  ON UPDATE CASCADE
  ON DELETE RESTRICT

) ENGINE=InnoDB

是的，我知道 - 你的脚本应该工作（即使它看起来没有多大意义）。然而，我认为这个新版本更好。

Tried it here and got the same error. This works though:

CREATE TABLE test2 (
  ID INT NOT NULL AUTO_INCREMENT,  
  col1 INT NOT NULL,
  col2 INT NOT NULL, 
  PRIMARY KEY (ID),
  CONSTRAINT fk FOREIGN KEY (col1)
                REFERENCES test1(ID)
  ON UPDATE CASCADE
  ON DELETE RESTRICT,
  CONSTRAINT fk2 FOREIGN KEY (col2)
                REFERENCES test1(ID)
  ON UPDATE CASCADE
  ON DELETE RESTRICT

) ENGINE=InnoDB

Yes, I know - your script should work (even if it doesn't seem to make much sense). Yet, I guess this new version is better.

回复收藏 0 原文

最后的乘客 2024-10-24 18:34:39

问题似乎是您在同一个外键（即（ID，ID））中两次指定相同的父列。以下应该可行：

Create Table Test1
    (
    PK1 int not null
    , PK2 int not null
    , Primary Key ( PK1, PK2 )
    )

Create Table Test2
    (
    Id int not null Auto_Increment
    , PK1 int not null
    , PK2 int not null
    , Primary Key ( ID )
    , Constraint FK_Test2
        Foreign Key ( PK1, PK2 )
        References Test1( PK1, PK2 )
    )

如果是这种情况，您希望子表中的两列引用相同的父表列，那么您必须添加两个外键引用，如 rsenna 所示，因为它们代表两个独立关系。

The problem would appear to be that you are specifying the same parent column twice in the same foreign key (i.e, (ID, ID)). The following should work:

Create Table Test1
    (
    PK1 int not null
    , PK2 int not null
    , Primary Key ( PK1, PK2 )
    )

Create Table Test2
    (
    Id int not null Auto_Increment
    , PK1 int not null
    , PK2 int not null
    , Primary Key ( ID )
    , Constraint FK_Test2
        Foreign Key ( PK1, PK2 )
        References Test1( PK1, PK2 )
    )

If it is the case, that you want two columns in a child table referencing the same parent table column, then you must add two foreign key references as shown by rsenna as those represent two independent relations.

回复收藏 0 原文

~没有更多了~