CODEWITHSUNDEEP
mysqlsql
user2522055
user2522055

Reputation: 159

Error Code: 1215 in mysql cannot add foreign key constraint

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)
);

Upvotes: 0

Views: 182

Answers (4)

echo_Me
echo_Me

Reputation: 37233

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

Upvotes: 1

santnair
santnair

Reputation: 11

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

Upvotes: 1

Chip Dean
Chip Dean

Reputation: 4302

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)
);

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

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)
);

Upvotes: 1

Related Questions