mySQL Help? Can anyone help correct this code? The error i am getting below ERRO

Question

mySQL Help?
Can anyone help correct this code?

The error i am getting below

ERROR 1215 (HY000): Cannot add foreign key constraint

# # inventory-schema.sql file
#
DROP DATABASE IF EXISTS inventory;
CREATE DATABASE inventory;
USE inventory;

# # is used for comment in MySQL
#DROP TABLE item_type;
CREATE TABLE item_type (
name varchar(25),
location varchar(25),
upc int(11),
weight int(11),
sku int(11),
item_price int(11),
qty int(11),
primary key (name),
unique (item_price)
);

#DROP TABLE owner; #CASCADE CONSTRAINTS;
CREATE TABLE owner (
name varchar(25),
birthday date,
ossn integer,
primary key (ossn)
);

#DROP TABLE employee; #CASCADE CONSTRAINTS;
CREATE TABLE employee (
name varchar(25) not null,
salary integer,
essn integer,
postion varchar(25),
owner varchar(25),
o_ssn integer not null,
manages_item varchar(25),
primary key (essn),
foreign key (o_ssn) references owner(ossn)
on delete set null
on update cascade
);

#DROP TABLE transaction; #CASCADE CONSTRAINTS;
CREATE TABLE transaction (
tid integer,
qty integer,
amount integer,
item varchar(25),
employee_name varchar(25) not null,
primary key (tid),
foreign key (employee_name) references employee(name)
on delete set null
on update cascade
);

#DROP TABLE payment_method; #CASCADE CONSTRAINTS;
CREATE TABLE payment_method (
type varchar(25),
card integer not null,
card_n integer not null,
cash integer,
check_1 integer,
checkn integer not null,
tid integer,
primary key (type),
unique key (tid)
);

#DROP TABLE card_type; #CASCADE CONSTRAINTS;
CREATE TABLE card_type (
name varchar(25),
card_number integer not null,
sec_code integer,
exp_date integer,
primary key (name)

);

#DROP TABLE check_type; #CASCADE CONSTRAINTS;
CREATE TABLE check_type (
name varchar(25),
check1 integer not null,
tid integer,
routing_number integer,
bank_number integer,
primary key (tid),
foreign key (check1) references payment_method(checkn)
on delete set null
on update cascade
);

#DROP TABLE cash; #CASCADE CONSTRAINTS;
CREATE TABLE cash (
amount integer,
tid integer,
primary key (amount)
);

Explanation / Answer

The problem is when your are importing your sql file the default engine for tables may be InnoDB.

InnoDB does not support foreign keys.It supports primary key.

So change engine of table containing forign key constraint to MyISAM. The working sql statements with MyISAM engines are given below:

Use blow sql statements:

DROP DATABASE IF EXISTS inventory;
CREATE DATABASE inventory;
USE inventory;
# # is used for comment in MySQL
#DROP TABLE item_type;
CREATE TABLE item_type (
name varchar(25),
location varchar(25),
upc int(11),
weight int(11),
sku int(11),
item_price int(11),
qty int(11),
primary key (name),
unique (item_price)
);

#DROP TABLE owner; #CASCADE CONSTRAINTS;
CREATE TABLE owner (
name varchar(25),
birthday date,
ossn integer,
primary key (ossn)
);

#DROP TABLE employee; #CASCADE CONSTRAINTS;
CREATE TABLE employee (
name varchar(25) not null,
salary integer,
essn integer,
postion varchar(25),
owner varchar(25),
o_ssn integer not null,
manages_item varchar(25),
primary key (essn),
foreign key (o_ssn) references owner(ossn)
on delete set null
on update cascade
)ENGINE=MyISAM;
#DROP TABLE transaction; #CASCADE CONSTRAINTS;
CREATE TABLE transaction (
tid integer,
qty integer,
amount integer,
item varchar(25),
employee_name varchar(25) not null,
primary key (tid),
foreign key (employee_name) references employee(name)
on delete set null
on update cascade
)ENGINE=MyISAM;
#DROP TABLE payment_method; #CASCADE CONSTRAINTS;
CREATE TABLE payment_method (
type varchar(25),
card integer not null,
card_n integer not null,
cash integer,
check_1 integer,
checkn integer not null,
tid integer,
primary key (type),
unique key (tid)
);
#DROP TABLE card_type; #CASCADE CONSTRAINTS;
CREATE TABLE card_type (
name varchar(25),
card_number integer not null,
sec_code integer,
exp_date integer,
primary key (name)
);
#DROP TABLE check_type; #CASCADE CONSTRAINTS;
CREATE TABLE check_type (
name varchar(25),
check1 integer not null,
tid integer,
routing_number integer,
bank_number integer,
primary key (tid),
foreign key (check1) references payment_method(checkn)
on delete set null
on update cascade
)ENGINE=MyISAM;
#DROP TABLE cash; #CASCADE CONSTRAINTS;
CREATE TABLE cash (
amount integer,
tid integer,
primary key (amount)
);

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.