无法添加外键约束 - MySQL ERROR 1215 (HY000)

Question

提问by Dhanuka

I am trying to create database for gym management system, but I can't figure out why I am getting this error. I've tried to search for the answer here, but I couldn't find it.

我正在尝试为健身房管理系统创建数据库，但我无法弄清楚为什么会出现此错误。我试图在这里寻找答案，但我找不到。

ERROR 1215 (HY000): Cannot add foreign key constraint

CREATE TABLE sales(
    saleId int(100) NOT NULL AUTO_INCREMENT,
    accountNo int(100) NOT NULL,
    payName VARCHAR(100) NOT NULL,
    nextPayment DATE,
    supplementName VARCHAR(250),
    qty int(11),
    workoutName VARCHAR(100),
    sDate datetime NOT NULL DEFAULT NOW(),
    totalAmount DECIMAL(11,2) NOT NULL,
    CONSTRAINT PRIMARY KEY(saleId, accountNo, payName),
    CONSTRAINT FOREIGN KEY(accountNo) REFERENCES accounts(accountNo) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FOREIGN KEY(payName) REFERENCES paymentFor(payName) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FOREIGN KEY(supplementName) REFERENCES supplements(supplementName) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FOREIGN KEY(workoutName) REFERENCES workouts(workoutName) ON DELETE CASCADE ON UPDATE CASCADE
);
    ALTER TABLE sales AUTO_INCREMENT = 2001;

Here is the parent tables.

这是父表。

CREATE TABLE accounts(
    accountNo int(100) NOT NULL AUTO_INCREMENT,
    accountType VARCHAR(100) NOT NULL,
    firstName VARCHAR(50) NOT NULL,
    lastName VARCHAR(60) NOT NULL,
    birthdate DATE NOT NULL,
    gender VARCHAR(7),
    city VARCHAR(50) NOT NULL,
    street VARCHAR(50),
    cellPhone VARCHAR(10),
    emergencyPhone VARCHAR(10),
    email VARCHAR(150) NOT NULL,
    description VARCHAR(350),
    occupation VARCHAR(50),
    createdOn datetime NOT NULL DEFAULT NOW(),
    CONSTRAINT PRIMARY KEY(accountNo)
);
    ALTER TABLE accounts AUTO_INCREMENT = 1001; 


CREATE TABLE supplements(
    supplementId int(100) NOT NULL AUTO_INCREMENT,
    supplementName VARCHAR(250) NOT NULL,
    manufacture VARCHAR(100),
    description VARCHAR(150),
    qtyOnHand INT(5),
    unitPrice DECIMAL(11,2),
    manufactureDate DATE,
    expirationDate DATE,
    CONSTRAINT PRIMARY KEY(supplementId, supplementName)
);
    ALTER TABLE supplements AUTO_INCREMENT = 3001;

CREATE TABLE workouts(
    workoutId int(100) NOT NULL AUTO_INCREMENT,
    workoutName VARCHAR(100) NOT NULL,
    description VARCHAR(7500) NOT NULL,
    duration VARCHAR(30),
    CONSTRAINT PRIMARY KEY(workoutId, workoutName)
);
    ALTER TABLE workouts AUTO_INCREMENT = 4001;



CREATE TABLE paymentFor(
    payId int(100) NOT NULL AUTO_INCREMENT,
    payName VARCHAR(100) NOT NULL,
    amount DECIMAL(11,2),
    CONSTRAINT PRIMARY KEY(payId, payName)
);
    ALTER TABLE paymentFor AUTO_INCREMENT = 5001;

Can you guys help me with this problem? Thanks.

你们能帮我解决这个问题吗？谢谢。

Answer 1

回答by John Cargo

If you ever want to find out, why that error was , all you have to do is run below command and look for "LATEST FOREIGN KEY ERROR"

如果您想找出错误的原因，您所要做的就是在命令下方运行并查找“最新外键错误”

Command to run :-

要运行的命令：-

mysql> SHOW ENGINE INNODB STATUS

You will know the reason for your such errors.

您将知道此类错误的原因。

Answer 2

回答by Ravinder Reddy

For a field to be defined as a foreign key, the referenced parent field must have an index defined on it.

对于要定义为 aforeign key的字段，引用的父字段必须在其上定义索引。

As per documentation on foreign keyconstraints:

根据有关foreign key约束的文档：

REFERENCES parent_tbl_name (index_col_name,...)

参考 parent_tbl_name (index_col_name,...)

Define an INDEXon workouts.workoutName, paymentFor.paymentName, and supplements.supplementNamerespectively. And make sure that child column definitions must match with those of their parent column definitions.

定义INDEX上workouts.workoutName，paymentFor.paymentName和supplements.supplementName分别。并确保子列定义必须与其父列定义匹配。

Change workoutstable definition as below:

更改workouts表定义如下：

CREATE TABLE workouts(
    workoutId int(100) NOT NULL AUTO_INCREMENT,
    workoutName VARCHAR(100) NOT NULL,
    description VARCHAR(7500) NOT NULL,
    duration VARCHAR(30),

    KEY ( workoutName ), -- <---- this is newly added index key

    CONSTRAINT PRIMARY KEY(workoutId, workoutName)
);

Change supplementstable definition as below:

更改supplements表定义如下：

CREATE TABLE supplements(
    supplementId int(100) NOT NULL AUTO_INCREMENT,
    supplementName VARCHAR(250) NOT NULL,
    manufacture VARCHAR(100),
    description VARCHAR(150),
    qtyOnHand INT(5),
    unitPrice DECIMAL(11,2),
    manufactureDate DATE,
    expirationDate DATE,

    KEY ( supplementName ), -- <---- this is newly added index key

    CONSTRAINT PRIMARY KEY(supplementId, supplementName)
);

Change paymentFortable definition as below:

更改paymentFor表定义如下：

CREATE TABLE paymentFor(
    payId int(100) NOT NULL AUTO_INCREMENT,
    payName VARCHAR(100) NOT NULL,
    amount DECIMAL(11,2),

    KEY ( payName ), -- <---- this is newly added index key

    CONSTRAINT PRIMARY KEY(payId, payName)
);

Now, change child table definition as below:

现在，更改子表定义如下：

CREATE TABLE sales(
    saleId int(100) NOT NULL AUTO_INCREMENT,
    accountNo int(100) NOT NULL,
    payName VARCHAR(100) NOT NULL,
    nextPayment DATE,
    supplementName VARCHAR(250) NOT NULL,
    qty int(11),
    workoutName VARCHAR(100) NOT NULL,
    sDate datetime NOT NULL DEFAULT NOW(),
    totalAmount DECIMAL(11,2) NOT NULL,
    CONSTRAINT PRIMARY KEY(saleId, accountNo, payName),
    CONSTRAINT FOREIGN KEY(accountNo) 
       REFERENCES accounts(accountNo) 
       ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FOREIGN KEY(payName) 
       REFERENCES paymentFor(payName) 
       ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FOREIGN KEY(supplementName) 
       REFERENCES supplements(supplementName) 
       ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FOREIGN KEY(workoutName) 
       REFERENCES workouts(workoutName) 
       ON DELETE CASCADE ON UPDATE CASCADE
);

Refer to:

参考：

MySQL Using FOREIGN KEY Constraints

MySQL 使用 FOREIGN KEY 约束

[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION

[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
参考选项：
限制 | 级联 | 置空 | 没有行动

Answer 3

回答by Dhanuka

Foreign Keysare a way of implementing relationships/constraints between columns in different tables.

外键是一种在不同表中的列之间实现关系/约束的方法。

There are different categories of constraints that influence how they're enforced when a row is updated or deleted from the parent table:

当从父表中更新或删除行时，有不同类别的约束会影响它们的执行方式：

?Cascade: If a row is deleted from the parent then any rows in the child table with a matching FK value will also be deleted. Similarly for changes to the value in the parent table.

? Cascade：如果从父表中删除一行，则子表中具有匹配 FK 值的任何行也将被删除。对父表中的值的更改也是如此。

?Restrict: A row cannot be deleted from the parent table if this would break a FK constraint with the child table. Similarly for changes to the value in the parent table.

? Restrict：如果这会破坏子表的 FK 约束，则无法从父表中删除行。对父表中的值的更改也是如此。

?No Action: Very similar to “Restrict” except that any events/triggers on the parent table will be executed before the constraint is enforced – giving the application writer the option to resolve any FK constraint conflicts using a stored procedure.

? No Action：与“限制”非常相似，除了将在强制执行约束之前执行父表上的任何事件/触发器 - 为应用程序编写者提供使用存储过程解决任何 FK 约束冲突的选项。

?Set NULL: If NULL is a permitted value for the FK column in the child table then it will be set to NULL if the associated data in the parent table is updated or deleted.

? Set NULL: 如果 NULL 是子表中 FK 列的允许值，那么如果父表中的关联数据被更新或删除，它将被设置为 NULL。

?Set Default: If there is a default value for the FK column in the child table then it will be used if the associated data in the parent table is updated or deleted. Note that this is not implemented in this version – the constraint can be added to the schema but any subsequent deletion or update to the column in the parent table will fail.

? Set Default: 如果子表中的 FK 列有默认值，那么在更新或删除父表中的关联数据时将使用该值。请注意，这在此版本中未实现 - 可以将约束添加到架构，但对父表中列的任何后续删除或更新都将失败。

Answer 4

回答by kadhiresan k

Some times you will get this error "#1215 - Cannot add foreign key constraint" because of table TYPE (InnoDB, MyISAM,..) mismatch.

有时您会收到此错误“#1215 - 无法添加外键约束”，因为表 TYPE (InnoDB, MyISAM,..) 不匹配。

So change your table type into same and try applying for foreign key constraint

因此，将您的表类型更改为相同并尝试申请外键约束

mysql> ALTER TABLE table_name ENGINE=InnoDB;

mysql> ALTER TABLE Orders ADD FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)

Answer 5

回答by edem

This might work for some people. Simply add the default character set as utf8

这可能对某些人有用。只需将默认字符集添加为 utf8

DEFAULT CHARACTER SET = utf8;

Answer 6

回答by Divije Narasimhachar

I was getting the same error. The reason was I was referring to a column in a table created with charset utf8 from a table created using charset latin.

我遇到了同样的错误。原因是我指的是使用 charset utf8 创建的表中的列，该表来自使用 charset latin 创建的表。

The tables created using mySQL workbench create table utility have default charset latin.

使用 mySQL 工作台创建表实用程序创建的表具有默认的拉丁字符集。

Easy approach to find this out if you are using workbench is to view the table create statement of any table. You will have the default charset string at the end.

如果您使用的是工作台，那么找出这一点的简单方法是查看任何表的表创建语句。您将在最后获得默认字符集字符串。

Answer 7

回答by Kemin Zhou

I encounter this error I add foreign key constraint for a column that has 'not null constraint' but I specified the 'on delete set null' in the foreign constraint. This is a contradiction that it may not be obvious at first.

我遇到了这个错误我为具有“非空约束”的列添加了外键约束，但我在外约束中指定了“on delete set null”。这是一个矛盾，一开始可能并不明显。

Here are my two tables:

这是我的两个表：

CREATE TABLE study (
    id int(11) NOT NULL AUTO_INCREMENT primary key,
    name varchar(100) NOT NULL,
    introduction text,
    objective varchar(250) DEFAULT NULL,
    method text,
    result text,
    conclusion varchar(250) DEFAULT NULL,
    future_action varchar(100) DEFAULT NULL
);

drop table client_study;
CREATE TABLE client_study (
      id int(11) NOT NULL AUTO_INCREMENT primary key,
      client_id int(11),
      study_id int(11) not null, --If delete 'not null' error goes away!
      contact_person int(11),
      effective_date datetime DEFAULT CURRENT_TIMESTAMP,
      trial_site int(11) DEFAULT NULL,
      UNIQUE KEY unqidx_client_study (client_id,study_id)
);

ALTER TABLE client_study
ADD CONSTRAINT FOREIGN KEY (study_id) REFERENCES study(id)
ON DELETE SET NULL ON UPDATE CASCADE;

ERROR 1215 (HY000): Cannot add foreign key constraint

If you remove the NOT NULL constraint on the study_id column in the client_study table, the foreign key can be added. The other alternative is to keep the not null constraint on the client_table, but modify the foreign key definition to on delete no action or other choices.

如果删除 client_study 表中 study_id 列的 NOT NULL 约束，则可以添加外键。另一种选择是在 client_table 上保留非空约束，但将外键定义修改为 on delete no action 或其他选择。

Answer 8

回答by Peter

I'm not answering the above question but just for people who will run into the same mysql error.

我不是在回答上面的问题，而是为那些会遇到同样的 mysql 错误的人回答。

All I did was to change the referenced table engine to innodb.

我所做的只是将引用的表引擎更改为 innodb。

无法添加外键约束 - MySQL ERROR 1215 (HY000)

提问by Dhanuka

回答by John Cargo

回答by Ravinder Reddy

回答by Dhanuka

回答by kadhiresan k

回答by edem

回答by Divije Narasimhachar

回答by Kemin Zhou

回答by Peter

相关推荐

最近更新

标签

无法添加外键约束 - MySQL ERROR 1215 (HY000)

提问by Dhanuka

回答by John Cargo

回答by Ravinder Reddy

回答by Dhanuka

回答by kadhiresan k

回答by edem

回答by Divije Narasimhachar

回答by Kemin Zhou

回答by Peter

相关推荐

MySQL 如何在mysql中自定义`show processlist`？

如何使 sql-mode="NO_ENGINE_SUBSTITUTION" 在 MySQL my.cnf 中永久存在

MySQL LEFT JOIN 作为新列？

MySQL SQL如何比较两个不同表中的两列

相关推荐

最近更新

标签