mysql - cannot add foreign key constraint when creating a table

Question

I'm new to sql.

I get this error when I try to create foreign keys:

cannot add foreign key constraint

when I'm trying to create the ORDERS table. here is my code:

drop database if exists Company;
create database Company;
use Company;
create table WORKERS(w_id varchar(4), w_name varchar(20) not null, telephone varchar(12), e_mail varchar(35) unique);
create table CUSTOMERS(customer_id varchar(4), customer_name varchar(20) not null, telephone varchar(12), e_mail varchar(35) unique, credit bool);
create table WAREHOUSE(m_id int unsigned primary key, describes varchar(20) not null, fl char(1));
create table ITEM(p_id int(4) unsigned primary key, p_name varchar(15) not null, p_price float not null);
create table INVENTORY(in_id int auto_increment primary key, m_id int unsigned not null, p_id int(4) unsigned not null, amount int not null,
foreign key (m_id) references WAREHOUSE(m_id),
foreign key (p_id) references ITEM(p_id)
);
create table ORDERS(o_id int auto_increment primary key, customer_id varchar(4),
p_id int(4) unsigned, w_id varchar(4), amount int unsigned not null, 
date_of_order date not null,
foreign key (p_id) references ITEM(p_id),
foreign key (w_id) references WORKERS(w_id),
foreign key (customer_id) references CUSTOMERS(customer_id)
);

I put the ORDERS (where I have the problem) on different lines to make it easier to you to read.

I search here for an answer, but didn't found anything specific that answer my question.

anyone got any idea what the problem is? thank you!

When you create a foreign key, the referenced column needs to be a primary key (or at least a unique key depending on the SQL that you're using). That's not the case for your Workers table. — Tom H
– Tom H, Commented May 3, 2016 at 14:37

Siyual · Accepted Answer · 2016-05-03 14:37:35Z

1

The problem is that you are trying to create a FOREIGN KEY relationship to a field in the WORKERS and CUSTOMERS tables that aren't set up as a PRIMARY KEY.

The FOREIGN KEY needs to be pointing to a PRIMARY KEY. Change your create script to the following:

create table WORKERS(w_id varchar(4) primary key, w_name varchar(20) not null, telephone varchar(12), e_mail varchar(35) unique);
create table CUSTOMERS(customer_id varchar(4) primary key, customer_name varchar(20) not null, telephone varchar(12), e_mail varchar(35) unique, credit bool);

A word of caution, though. I would recommend not using VARCHAR (4) as a PRIMARY KEY field. I would recommend using an INT AUTO_INCREMENT instead.

answered May 3, 2016 at 14:37

Siyual

17k8 gold badges47 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

moshem Over a year ago

thanks a lot! and yes, I'm agree with you it is better to be auto_increment - but my school want it to be that way... I'm not really sure why.

Barmar Over a year ago

Foreign key doesn't have to point to a primary key, it just needs to be an indexed column.

Prerak Sola · Accepted Answer · 2016-05-03 14:38:35Z

1

The column of the referenced table should be declared as primary key to assign a foreign key. For your orders table, WORKERS(w_id) and CUSTOMERS(customer_id) are not declared as primary key, hence you get the error.

Modified Statements:

drop database if exists Company;
create database Company;
use Company;
create table WORKERS(w_id varchar(4) primary key, w_name varchar(20) not null, telephone varchar(12), e_mail varchar(35) unique);
create table CUSTOMERS(customer_id varchar(4) primary key, customer_name varchar(20) not null, telephone varchar(12), e_mail varchar(35) unique, credit bool);
create table WAREHOUSE(m_id int unsigned primary key, describes varchar(20) not null, fl char(1));
create table ITEM(p_id int(4) unsigned primary key, p_name varchar(15) not null, p_price float not null);
create table INVENTORY(in_id int auto_increment primary key, m_id int unsigned not null, p_id int(4) unsigned not null, amount int not null,
foreign key (m_id) references WAREHOUSE(m_id),
foreign key (p_id) references ITEM(p_id)
);

create table ORDERS(o_id int auto_increment primary key, customer_id varchar(4),
p_id int(4) unsigned, w_id varchar(4), amount int unsigned not null, 
date_of_order date not null,
foreign key (p_id) references ITEM(p_id),
foreign key (w_id) references WORKERS(w_id),
foreign key (customer_id) references CUSTOMERS(customer_id)
);

SQLFiddle Demo

answered May 3, 2016 at 14:38

Prerak Sola

10.1k8 gold badges39 silver badges77 bronze badges

1 Comment

Barmar Over a year ago

The referenced column just has to have an index, it doesn't have to be a primary key, although usually it is.

Collectives™ on Stack Overflow

mysql - cannot add foreign key constraint when creating a table

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related