CODEWITHSUNDEEP
mysqlsqlerror-handlingforeign-keys
Lyu Keting
Lyu Keting

Reputation: 83

mysql: ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

drop table if exists Users;
drop table if exists Item;
drop table if exists Bid;
drop table if exists Category;

create table Users (
    userID varchar(200),
    rating integer,
    location varchar(255),
    country varchar(150),
    PRIMARY KEY(userID)
);

create table Item (
    itemID integer,
    name varchar(100),
    currently float,
    buy_price float,
    first_bid float,
    started timestamp,
    ends timestamp,
    userID varchar(200),
    description varchar(255),
    PRIMARY KEY(itemID),
    FOREIGN KEY (userID) REFERENCES Users(userID)
);

create table Bid (
    itemID integer,
    userID varchar(200),
    time varchar(180),
    amount float,
    PRIMARY KEY(itemID,time),
    FOREIGN KEY (userID) REFERENCES Users(userID),
    FOREIGN KEY (itemID) REFERENCES Item(itemID)
);

create table Category(
    itemID integer,
    categoryID varchar(220),
    PRIMARY KEY(itemID,categoryID),
    FOREIGN KEY (itemID) REFERENCES Item(itemID)
);

I have a database named klyu. Above is a file named create.sql, when i run

source create.sql

in mysql, it returns

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign   key constraint fails
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.17 sec)

ERROR 1050 (42S01): Table 'Users' already exists
ERROR 1050 (42S01): Table 'Item' already exists
Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.02 sec)

I saw some guy say check this via using SHOW ENGINE INNODB STATUS after drop fails, however, when i run this, it says ERROR 1227 (42000): Access denied; you need the PROCESS privilege for this operation

I have checked very carefully and read manual of foreign keys, but i still cannot find where is the problem.

Upvotes: 1

Views: 6720

Answers (1)

Srikanth Balaji
Srikanth Balaji

Reputation: 2718

When you try to create a table Item and reference with user ID as foreign key and the User table is still not created, you are facing this error. Refer this - MySQL - FOREIGN KEY Constraints Documentation:

You have to reorder the script.

drop table if exists Item;
drop table if exists Users;
drop table if exists Bid;
drop table if exists Category; 

create table Users (
    userID varchar(200),
    rating integer,
    location varchar(255),
    country varchar(150),
    PRIMARY KEY(userID)
)   ENGINE=INNODB;

create table Item (
    itemID integer,
    name varchar(100),
    currently float,
    buy_price float,
    first_bid float,
    started timestamp,
    ends timestamp,
    userID varchar(200),
    description varchar(255),
    PRIMARY KEY(itemID),
    FOREIGN KEY (userID) REFERENCES Users(userID)
)   ENGINE=INNODB;


create table Bid (
    itemID integer,
    userID varchar(200),
    time varchar(180),
    amount float,
    PRIMARY KEY(itemID,time),
    FOREIGN KEY (userID) REFERENCES Users(userID),
    FOREIGN KEY (itemID) REFERENCES Item(itemID)
)   ENGINE=INNODB;

create table Category(
    itemID integer,
    categoryID varchar(220),
    PRIMARY KEY(itemID,categoryID),
    FOREIGN KEY (itemID) REFERENCES Item(itemID)
)   ENGINE=INNODB;

Upvotes: 3

Related Questions