Error Code: 1215 in mysql cannot add foreign key constraint

Question

I am getting an issue and I feel like I fixed everything. MySQL workbench is still giving me this error. If you can please explain what is wrong I would be greatly appreciate it. I did check for key constraints in the Account table. It is giving the error in the account table.

CREATE TABLE Account(
    AcctNum int AUTO_INCREMENT,
    MemberID int,
    Balance double,
    PIN int,
    creationDate date,
    InitialBalance double,
    CreatedByEmployee int,
    type VARCHAR(20),
    PRIMARY KEY(AcctNum),
    FOREIGN KEY(MemberID) REFERENCES Member(MemNum),
    FOREIGN KEY(CreatedByEmployee) REFERENCES Employee(EmpId)
);

CREATE TABLE Member( 
    MemNum int AUTO_INCREMENT,  
    DOB date, 
    CreditScore int, 
    AcctOpened date, 
    SSN VARCHAR(11),  
    Address VARCHAR(255),
    PRIMARY KEY(MemNum)
);

CREATE TABLE Employee( 
    EmpId int AUTO_INCREMENT,  
    DOB date, 
    SSN VARCHAR(11), 
    HireDate date, 
    Salary double, 
    EmpLevel VARCHAR(50),
    PRIMARY KEY(EmpId)
);

The Member table and Employee table has to exist before reference. i.e., Create both of these tables before creating Account table. — Raging Bull
– Raging Bull, Commented May 4, 2014 at 18:21

Abhik Chakraborty · Accepted Answer · 2014-05-04 18:17:28Z

You need to create the referencing table first before creating the table which is referring other tables with FOREIGN key.

The order of table creation should be as

CREATE TABLE Member( 
    MemNum int AUTO_INCREMENT,  
    DOB date, 
    CreditScore int, 
    AcctOpened date, 
    SSN VARCHAR(11),  
    Address VARCHAR(255),
    PRIMARY KEY(MemNum)
);

CREATE TABLE Employee( 
    EmpId int AUTO_INCREMENT,  
    DOB date, 
    SSN VARCHAR(11), 
    HireDate date, 
    Salary double, 
    EmpLevel VARCHAR(50),
    PRIMARY KEY(EmpId)
);

CREATE TABLE Account(
    AcctNum int AUTO_INCREMENT,
    MemberID int,
    Balance double,
    PIN int,
    creationDate date,
    InitialBalance double,
    CreatedByEmployee int,
    type VARCHAR(20),
    PRIMARY KEY(AcctNum),
    FOREIGN KEY(MemberID) REFERENCES Member(MemNum),
    FOREIGN KEY(CreatedByEmployee) REFERENCES Employee(EmpId)
);

Chip Dean · Accepted Answer · 2014-05-04 18:18:11Z

You are creating the Foreign key for Member before creating the table so it does not exist yet. Change the order you are creating the tables.

CREATE TABLE Member( 
    MemNum int AUTO_INCREMENT,  
    DOB date, 
    CreditScore int, 
    AcctOpened date, 
    SSN VARCHAR(11),  
    Address VARCHAR(255),
    PRIMARY KEY(MemNum)
);

CREATE TABLE Employee( 
    EmpId int AUTO_INCREMENT,  
    DOB date, 
    SSN VARCHAR(11), 
    HireDate date, 
    Salary double, 
    EmpLevel VARCHAR(50),
    PRIMARY KEY(EmpId)
);
CREATE TABLE Account(
    AcctNum int AUTO_INCREMENT,
    MemberID int,
    Balance double,
    PIN int,
    creationDate date,
    InitialBalance double,
    CreatedByEmployee int,
    type VARCHAR(20),
    PRIMARY KEY(AcctNum),
    FOREIGN KEY(MemberID) REFERENCES Member(MemNum),
    FOREIGN KEY(CreatedByEmployee) REFERENCES Employee(EmpId)
);

santnair · Accepted Answer · 2014-05-04 18:33:45Z

1

Ofcourse the order of table creation is important here. Create the Member and Employee tables first, followed by the Account. It should work fine.

answered May 4, 2014 at 18:33

santnair

113 bronze badges

Comments

echo_Me · Accepted Answer · 2014-05-04 18:59:37Z

1

you have wrong order of creating tables

try this

1- Create member table first

2- employer table second

3- account table in last

http://www.sqlfiddle.com/#!2/47d58

edited May 4, 2014 at 18:59

answered May 4, 2014 at 18:18

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

Collectives™ on Stack Overflow

Error Code: 1215 in mysql cannot add foreign key constraint

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related