mysql: ERROR 1217 (23000): 无法删除或更新父行:外键约束失败

Question

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)
);

我有一个名为 klyu 的数据库。上面是一个名为create.sql的文件，当我运行

source create.sql

在mysql中，它返回

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)

我看到有人说在删除失败后通过使用 SHOW ENGINE INNODB STATUS 检查这个，但是，当我运行它时，它说 ERROR 1227 (42000): Access denied;您需要此操作的 PROCESS 权限

我已经非常仔细地检查并阅读了外键手册，但我仍然找不到问题所在。

Answer 1

当您尝试创建一个表 Item 并引用用户 ID 作为外键并且用户表仍未创建时，您将面临此错误。请参阅此 - MySQL - FOREIGN KEY Constraints Documentation:

您必须重新排序脚本。

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;