Cannot add foreign key constraint - MySQL ERROR 1215 (HY000)

Question

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

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

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