MySQL Error 1215 : Cannot add foreign key constraint

Question

I am trying to forward a new schema to my db server, but I do not know why this error is occurring. I tried to find an answer to this, but everything I found was either a db engine in Innodb or a key in my table that I was trying to use as a foreign key. If I didn't do it wrong, I did both. Can you help me with something else?

Executing SQL script in serve

ERROR: Error 1215:

-- -----------------------------------------------------    
-- Table 'Alternative_Pathways'.'Clients_has_Staff'
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS 'Alternative_Pathways'.'Clients_has_Staff' 
(
'Clients_Case_Number' INT NOT NULL, 
'Staff_Emp_ID' INT NOT NULL,
PRIMARY KEY ('Clients_Case_Number', 'Staff_Emp_ID'),
INDEX 'fk_Clients_has_Staff_Staff1_idx'('Staff_Emp_ID' ASC),
INDEX 'fk_Clients_has_Staff_Clients_idx' ('Clients_Case_Number'),
CONSTRAINT 'fk_Clients_has_Staff_Clients'
FORIGN KEY ('Client_Case_Number')
REFERENCE 'Alternative_Pathways'.'Clients' ('Case_Number')
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT 'fk_Clients_has_Staff_Staff1'
FORREIGN KEY ('Staff_Emp_ID')
REFERENCES 'Alternative_Pathways'.'Staff' ('Emp_ID')
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
SQL Script Execution Complete: Statement: 7 Successful, 1 Failed

The following are SQL for the parent table:

CREATE TABLE IF NOT EXISTS 'Alternative_Pathways'.'Clients' (
'Case_Number' INT NOT NULL,
'First_Name' CHAR (10) NULL,
'Middle_Name' CHAR (10) NULL,
'Last_Name' CHAR (10) NULL,
'Address' CHAR (50) NULL,
'Phone_Number' INT (10) NULL,
PRIMARY KEY ('Case_Number') )
ENGINE = InnoDB

CREATE TABLE IF NOT EXISTS 'Alternative_Pathways.'(
'Emp_ID' INT NOT NULL,
'First_Name' CHAR (10) NULL,
'Middle_Name' CHAR (10) NULL,
'Last_Name' CHAR (10) NULL,
PRIMARY KEY ('Emp_ID') )
ENGINE = InnoDB

If either of the answers work for you, please give closer to your question by marking one of the answers as accepted. — zedfoxus
– zedfoxus, Commented Dec 6, 2018 at 3:09

zedfoxus · Accepted Answer · 2018-12-06 02:51:23Z

Try this:

Start with using your schema

use Alternative_Pathways;

Parent table Clients

Remove the single quotations. You could use backticks instead of single quotation.

CREATE TABLE IF NOT EXISTS Clients (
    Case_Number INT NOT NULL,
    First_Name CHAR (10) NULL,
    Middle_Name CHAR (10) NULL,
    Last_Name CHAR (10) NULL,
    Address CHAR (50) NULL,
    Phone_Number INT (10) NULL,
    PRIMARY KEY (Case_Number)
)
ENGINE = InnoDB;

Parent table Staff

CREATE TABLE IF NOT EXISTS Staff (
    Emp_ID INT NOT NULL,
    First_Name CHAR (10) NULL,
    Middle_Name CHAR (10) NULL,
    Last_Name CHAR (10) NULL,
    PRIMARY KEY (Emp_ID)
)
ENGINE = InnoDB;

Table with constraints

Notice that I've corrected typos.

CREATE TABLE IF NOT EXISTS Clients_has_Staff 
(
    Clients_Case_Number INT NOT NULL, 
    Staff_Emp_ID INT NOT NULL,
    PRIMARY KEY (Clients_Case_Number, Staff_Emp_ID),
    INDEX fk_Clients_has_Staff_Staff1_idx(Staff_Emp_ID ASC),
    INDEX fk_Clients_has_Staff_Clients_idx (Clients_Case_Number),
    CONSTRAINT fk_Clients_has_Staff_Clients FOREIGN KEY (Clients_Case_Number) REFERENCES Clients (Case_Number)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
    CONSTRAINT fk_Clients_has_Staff_Staff1 FOREIGN KEY (Staff_Emp_ID) REFERENCES Staff (Emp_ID)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
)
ENGINE = InnoDB;

This works on MySQL 5.7.17.

김미지 · Accepted Answer · 2018-12-05 17:41:21Z

0

External key constraint errors can occur for the following reasons:

Do not use InnoDB as an engine on all tables. Attempt to reference a key that does not exist in the target table. Please check the key recognition in the other table (can be a primary or unique key) Column type is not the same (exception is that column in reference table can enter null). If PK / FK is varchar, both data alignment must be the same. Up-to-date:

One reason may be that the column in use for ON DELETE SET NULL is not defined as null. Therefore, make sure that the column is set to the default value of null. Check these out.

answered Dec 5, 2018 at 17:41

김미지

12 bronze badges

Collectives™ on Stack Overflow

MySQL Error 1215 : Cannot add foreign key constraint

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related