MySQL #1215 Issue

Question

I keep getting an issue with creating this table in my database. The issue is:

Error:1215 Cannot add foreign key constraint.

This is the table I am trying to make:

CREATE TABLE Customer ( customer_reference int UNIQUE AUTO_INCREMENT, primary key (customer_reference), forename VARCHAR(20), surname VARCHAR(20), contact VARCHAR(15), email VARCHAR(50), building VARCHAR(5), road VARCHAR(40), city VARCHAR(30), postcode VARCHAR(7), county VARCHAR(30));

CREATE TABLE Invoice ( invoice_reference int UNIQUE AUTO_INCREMENT, customer_reference int UNIQUE, primary key (invoice_reference), foreign key (customer_reference) references Customer(customer_reference), invoice_cost DECIMAL(20,2), paid bit, order_date DATETIME, delivery_date DATE);

CREATE TABLE Stock ( container VARCHAR(10) UNIQUE NOT NULL DEFAULT 0, primary key (container), SBADNLon INT(4), SBADNFel INT(4), SBADNSou INT(4), CHECK (container = ("SBADN-Lon" > 0, "SBADN-Fel" > 0, "SBADN-Sou" > 0))); /* This is just showing 3 of the possible container variations Each attribute stores a value containing the number of that model available in inventory */

CREATE TABLE Items_Purchased ( container_ordered VARCHAR(10) NOT NULL, invoice_reference int, container VARCHAR(10) NOT NULL DEFAULT "None", container_cost decimal(20,2) NULL, container_size VARCHAR(6) NOT NULL, colour VARCHAR(5) NOT NULL, grade CHAR(1) NOT NULL, depot VARCHAR(15) NOT NULL, container_type VARCHAR(20) NOT NULL, conditionn VARCHAR(4) NOT NULL, primary key (container_ordered, container_size, colour, grade, depot, container_type, conditionn), foreign key (invoice_reference) references Invoice (invoice_reference), foreign key (container) references Stock (container), foreign key (container_size) references Container_Size (container_size), foreign key (colour) references Colour (colour), foreign key (grade) references Grade (grade), foreign key (depot) references Depot (depot), foreign key (container_type) references Container_Type (container_type), foreign key (conditionn) references Conditionn (conditionn));

CREATE TABLE Depot ( depot VARCHAR(15) NOT NULL, container_ordered VARCHAR(10) NOT NULL, primary key (depot), foreign key (container_ordered) references Items_Purchased(container_ordered), CHECK (depot = ("london","felixstowe","southampton")));

CREATE TABLE Container_Type ( container_type VARCHAR(20) NOT NULL, container_ordered VARCHAR(10) NOT NULL, primary key (container_type), foreign key (container_ordered) references Items_Purchased(container_ordered), CHECK (container_type = ("dry","inslated","refreigerated","open top","tunnel")));

CREATE TABLE Container_Size ( container_size VARCHAR(6) NOT NULL, container_ordered VARCHAR(10) NOT NULL, primary key (container_size), foreign key (container_ordered) references Items_Purchased(container_ordered), CHECK (container_size = ("small","medium","large")));

CREATE TABLE Colour ( colour VARCHAR(5) NOT NULL, container_ordered VARCHAR(10) NOT NULL, primary key (colour), foreign key (container_ordered) references Items_Purchased(container_ordered), CHECK (colour = ("black","green")));

CREATE TABLE Conditionn ( conditionn VARCHAR(4) NOT NULL, container_ordered VARCHAR(10) NOT NULL, primary key (conditionn), foreign key (container_ordered) references Items_Purchased(container_ordered), CHECK (conditionn = ("new","used")));

CREATE TABLE Grade ( grade CHAR(1) NOT NULL, container_ordered VARCHAR(10) NOT NULL, primary key (grade), foreign key (container_ordered) references Items_Purchased(container_ordered), CHECK (grade = ("a","b","c")));

Thanks in advance

check the datatypes of both the columns used in foreign key reference. it should be same — Sas, Apr 02 '18 at 13:14
All tables appear to be successfully created, there is just an issue with this one — Jok56, Apr 02 '18 at 13:19
Have checked all data types in referenced tables and all are the same — Jok56, Apr 02 '18 at 13:19
Check all the conditions here have been met https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html — P.Salmon, Apr 02 '18 at 13:27

score 0 · Answer 1 · answered Apr 02 '18 at 13:19

First of all, I believe it would be better to use another primary key for your table.

The datatypes of all the foreign key constraints should be exactly the same as how the fields are defined as primary keys in the original table. If container is varchar(20) in Stock table, for example, it has to be varchar(20) in Items_Purchased table.

Also, the collations defined (if any) would be the same, like utf-8 for those columns. Note that your tables might be in the same collaction, but the columns might have different, check properly.

Lastly, make sure the values for the foreign key values are unique and the definition of foreign key columns include not null

Reference: MySQL Error 1215: Cannot add foreign key constraint

Thank you, Looks as though all the tables below have not been created, however when I try to create them, it gives me 1215 — Jok56, Apr 02 '18 at 13:25

kc2018 · Answer 2 · 2018-04-02T13:54:13.867

0

the following 2 rules might have caused the error:

invoice_reference int UNIQUE auto_increment
foreign key (invoice_reference) references Invoice (invoice_reference)

The auto_increment property should be specified in the Invoice table (invoice_reference) instead.

edited Apr 02 '18 at 13:54

answered Apr 02 '18 at 13:48

kc2018

1,440
1
8
8

should invoice_reference just be defined as INT then? – Jok56 Apr 02 '18 at 14:01
Yes and move the auto_increment to the invoice_reference in the Invoice table if you want to keep the auto increment feature. – kc2018 Apr 02 '18 at 14:07
Thanks, however problem still persists – Jok56 Apr 02 '18 at 14:11
Did you remove the auto_increment? If so, remove be the constraint to test if that fk constraint is the problem. – kc2018 Apr 02 '18 at 14:15
I would also remove the UNIQUE property. – kc2018 Apr 02 '18 at 14:16
Yes, just put: invoice_reference int, – Jok56 Apr 02 '18 at 14:32
What do you mean? "Did you remove the auto_increment? If so, remove be the constraint to test if that fk constraint is the problem." – Jok56 Apr 02 '18 at 14:33
I meant remove `foreign key (invoice_reference)...` Just to test if that is the line that caused the problem. – kc2018 Apr 02 '18 at 14:45
Still no fix here :( – Jok56 Apr 04 '18 at 17:11
Did you try removing all the foreign key references and then add one at a time to test which one is causing the error? – kc2018 Apr 04 '18 at 17:35
Are the foreign keys defined as the primary key of the referenced tables? Take a look at this [demo](http://sqlfiddle.com/#!9/f6462f). If you remove `PRIMARY KEY` in table `STOCK` (for example), you get the error "cannot add foreign key constraint'. – kc2018 Apr 04 '18 at 17:44
[f you really want to create a foreign key to a non-primary key, it MUST be a column that has a unique constraint on it.］(https://stackoverflow.com/questions/18435065/foreign-key-to-non-primary-key) – kc2018 Apr 04 '18 at 20:24

score 0 · Answer 3 · answered Apr 06 '18 at 11:33

0

I tried inserting each foreign key definition separately using the ALTER table command and it took it well - maybe some form of referencing issue?

answered Apr 06 '18 at 11:33

Jok56

69
1
9

score 0 · Answer 4 · answered Apr 06 '18 at 11:56

My guess is, See in order to create a foreign key reference, the referenced table must be created before its reference is created. For example, check your third table. It contains a customer reference and it is working fine.

Now if you see DEPOT table and Items_Purchased table, both contains foreign key references on each other. Now think about it, In order to reference from Depot to Items_Purchased, Items_purchased must be present before Depot, and the vice versa must be true as well for referencing Items_Purchased to Depot. This will never be possible.

Please reconstruct your schema accordingly, and sort out which table should be created first, in order to get reference from that table.

This is more like a forward referencing error you face while compiling code in Java.

MySQL #1215 Issue

4 Answers4