无法添加外键约束 - MySQL ERROR 1215（HY000）

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.

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

8 个解决方案

#1

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

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

As per documentation on foreign key constraints:

根据外键约束的文档：

REFERENCES parent_tbl_name (index_col_name,...)

参考parent_tbl_name（index_col_name，...）

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

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

Change workouts table definition as below:

更改锻炼表定义如下：

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 supplements table definition as below:

更改补充表定义如下：

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 paymentFor table 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]

[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

reference_option：RESTRICT | CASCADE | SET NULL |没有行动

#2

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.

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

#3

Foreign Keys are 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.

◾NoAction：非常类似于“Restrict”，除了在强制执行约束之前将执行父表上的任何事件/触发器 - 为应用程序编写者提供使用存储过程解决任何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.

◾SetNULL：如果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.

◾设置默认值：如果子表中的FK列有默认值，则在更新或删除父表中的关联数据时将使用它。请注意，此版本中未实现此操作 - 可以将约束添加到架构中，但是对父表中的列的任何后续删除或更新都将失败。

#4

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 table_name ENGINE = InnoDB;

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

mysql> ALTER TABLE Orders ADD FOREIGN KEY（P_Id）REFERENCES人员（P_Id）

#5

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

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

DEFAULT CHARACTER SET = utf8;

#6

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 latin创建的表中使用charset utf8创建的表中的列。

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

使用mySQL workbench create table实用程序创建的表具有默认的charset latin。

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.

如果您正在使用工作台，那么轻松找到它的方法是查看任何表的表create语句。您将在末尾使用默认的字符集字符串。

#7

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。

#8

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.

我遇到这个错误我为具有'not null constraint'的列添加外键约束但是我在外部约束中指定了'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上保留非空约束，但修改外键定义为不删除任何操作或其他选择。