moshem moshem - 7 months ago 11
SQL Question

mysql - cannot add foreign key constraint when creating a table

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!

Answer

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.

Comments