Cannot add foreign key constraint - MySQL ERROR 12

问题:

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.

回答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,...)

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.

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:

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

[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

回答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.

◾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.

◾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.

◾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 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.

回答4:

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

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)

回答5:

This might work for some people. Simply add the default character set as 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.

The tables created using mySQL workbench create table utility have default 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.

回答7:

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

All I did was to change the referenced table engine to 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.

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.