MySQL #1215 Issue

1
votes

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

4
mysqlsqldatabaseforeign-keys
check the datatypes of both the columns used in foreign key reference. it should be same – Sas
are you sure the parent tables exist? – jose_bacoy
All tables appear to be successfully created, there is just an issue with this one – Jok56
Have checked all data types in referenced tables and all are the same – Jok56
Check all the conditions here have been met dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html – P.Salmon

4 Answers

0
votes

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

0
votes

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.

0
votes

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

0
votes

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.