MySQL错误1215无法在不同的表中添加外键约束 - FK [英] MySQL error 1215 Cannot add Foreign key constraint - FK in different tables

问题描述

在mysql工作台上，  CREATE TABLE Utilizador（email varchar（40）not null，nome varchar（50）
 not null，dataNascimento date，profissao varchar（50）not null，
 reputacao double（3， 2）unsigned not null，constraint pk_Utilizador 
 primary key（email））
  

这是创建第一个表！

  CREATE TABLE POI（email varchar（40）not null，designacaoPOI 
 varchar（10）not null，coordenadaX int，coordenadaY int，
 descricaoPOI varchar（200），约束pk_POI主键（email，
 designacaoPOI），约束fk_POI外键（email）引用
 Utilizador（email）on delete cascade）
  

这是创建的第二张表！

  CREATE TABLE Utilizador_POI（email varchar（40）not null，designacaoPOI 
 varchar（10）not null，constrai nt pk_Utilizador_POI主键（email，
 designacaoPOI），约束fk1_Utilizador_POI外键（email）
引用Utilizador（email）删除级联，约束
 fk2_Utilizador_POI外键（designacaoPOI）引用
 POI（designacaoPOI）删除级联）
  

这张表给了我错误：Error Code：1215. Can not添加外键约束

我做了一些测试，我几乎可以肯定问题是在外键designacaoPOI。其他FK（电子邮件）不给我任何错误，所以也许问题是在表兴趣点？

感谢高级！

解决方案

这里的问题是双重的：
$ b

1使用 ID s PRIMARY KEY s

您应该使用 ID为主键而不是 VARCHAR 或任何具有真实世界业务含义的事物。如果您希望电子邮件在 Utilizador 表中是唯一的，则 email 和 designacaoPOI 在 POI 表中是唯一的，相同的组合（ email 和 designacaoPOI ）在 Utilizador_POI 中是唯一的，您应该使用 UNIQUE KEY 约束，而不是 PRIMARY KEY 约束





2 > DELETE CASCADE 在> FOREIGN KEY 上不能引用 > PRIMARY KEY

在您的第三个表格 Utilizador_POI 有两个 FOREIGN KEY s引用 POI 。不幸的是， POI 上的 PRIMARY KEY 是一个组合键，所以MySQL不知道如何处理 DELETE CASCADE ，因为中的 FOREIGN KEY 之间没有一对一的关系Utilizador_POI 以及 POI 的 PRIMARY KEY 。

如果您将表更改为所有的 PRIMARY KEY ID ，如下所示：

  CREATE TABLE blah（
 id INT（9）AUTO_INCREMENT NOT NULL 
 .... 
 PRIMARY KEY（id）
）; 
  

然后您可以通过 ID引用每个表 ，并且你的 FOREIGN KEY 和 DELETE CASCADE s都可以使用。

im new on mysql workbench, and i tried so many things to put my script working but i simply cant... Ive got these tables:

CREATE TABLE Utilizador (email varchar(40) not null, nome varchar(50) 
not null,  dataNascimento date,  profissao varchar(50) not null,      
reputacao double(3,2) unsigned not null, constraint pk_Utilizador     
primary key(email))

This is the first table created!

CREATE TABLE POI (email varchar(40) not null,  designacaoPOI
varchar(10) not null,  coordenadaX int,  coordenadaY int, 
descricaoPOI varchar(200), constraint pk_POI primary key(email,
designacaoPOI), constraint fk_POI foreign key(email) references
Utilizador(email) on delete cascade)

This is the second table created!

CREATE TABLE Utilizador_POI (email varchar(40) not null, designacaoPOI
varchar(10) not null, constraint pk_Utilizador_POI primary key(email,
designacaoPOI), constraint fk1_Utilizador_POI foreign key(email)
references Utilizador(email) on delete cascade,  constraint
fk2_Utilizador_POI foreign key(designacaoPOI) references
POI(designacaoPOI) on delete cascade)

This table gives me the error: Error Code: 1215. Cannot add foreign key constraint

I did some tests and im almost sure that the problem is in the foreign key "designacaoPOI". The other FK ("email") dont give me any error, so maybe the problem is in the Table POI?

Thanks in advanced!

解决方案

The problem here is twofold:

1/ Use IDs for PRIMARY KEYs

You should be using IDs for primary keys rather than VARCHARs or anything that has any real-world "business meaning". If you want the email to be unique within the Utilizador table, the combination of email and designacaoPOI to be unique in the POI table, and the same combination (email and designacaoPOI) to be unique in Utilizador_POI, you should be using UNIQUE KEY constraints rather than PRIMARY KEY constraints.

2/ You cannot DELETE CASCADE on a FOREIGN KEY that doesn't reference the PRIMARY KEY

In your third table, Utilizador_POI, you have two FOREIGN KEYs references POI. Unfortunately, the PRIMARY KEY on POI is a composite key, so MySQL has no idea how to handle a DELETE CASCADE, as there is not a one-to-one relationship between the FOREIGN KEY in Utilizador_POI and the PRIMARY KEY of POI.

If you change your tables to all have a PRIMARY KEY of ID, as follows:

CREATE TABLE blah (
   id    INT(9)    AUTO_INCREMENT    NOT NULL
   ....
   PRIMARY KEY (id)
);

Then you can reference each table by ID, and both your FOREIGN KEYs and DELETE CASCADEs will work.

这篇关于MySQL错误1215无法在不同的表中添加外键约束 - FK的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！