CODEWITHSUNDEEP
mysql
Penny Zhang
Penny Zhang

Reputation: 1

MySQL error 1215 cannot add foriegn key constraint

USE sales;
CREATE TABLE sales
(
   purchase_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
   date_of_purchase DATE,
   customer_id INT,
   idem_code VARCHAR(10)
);

CREATE TABLE customers
(
   customer_id INT,
   first_name VARCHAR(255),
   last_name VARCHAR(255),
   email_address VARCHAR(255),
   number_of_customer INT
);

ALTER TABLE sales
ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE;

I am a complete my SQL beginner. I came across 1215 my SQL error and have tried various ways to fix it but in vain. Please kindly help.

Thank you so much.

Upvotes: 0

Views: 21

Answers (1)

Cid
Cid

Reputation: 15247

A Foreign key constraint must reference something UNIQUE.

In your customers table, the customer_id is a simple INT, there is nothing enforcing uniqueness.

I would strongly advise you to change the table customers definition to :

CREATE TABLE customers
(
   customer_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -- <---- Check this line
   first_name VARCHAR(255),
   last_name VARCHAR(255),
   email_address VARCHAR(255),
   number_of_customer INT
);

INT NOT NULL PRIMARY KEY AUTO_INCREMENT is a common way to write INT primary keys in MySQL, the same way you did in the table sales. You can use BIGINT instead of INT

Upvotes: 1

Related Questions