MySQL 错误 1217

Question

这个问题已经有答案了:

MySQL Error 1005? (1 个回答)

已关闭 7 年前。

我收到此错误，并且我知道它来自 DROP TABLE IF 语句，但我不知道如何组织删除以使其正常工作。我尝试过重新排列它们，但行不通。当我引入 Passenger 表时出现问题，但我不知道如何修复它。

DROP TABLE IF EXISTS `Passenger`;
DROP TABLE IF EXISTS `ProductInvoice`;
DROP TABLE IF EXISTS `Invoice`;
DROP TABLE IF EXISTS `Product`;
DROP TABLE IF EXISTS `Customer`;
DROP TABLE IF EXISTS `Person`;
DROP TABLE IF EXISTS `Airport`;
DROP TABLE IF EXISTS `Address`;
DROP TABLE IF EXISTS `Email`;


CREATE TABLE Address
(
AddressID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(AddressID),
AddressStreet VARCHAR(255),
AddressCity VARCHAR(255),
AddressState VARCHAR(255),
AddressZip VARCHAR(255),
AddressCountry VARCHAR(255)
);


CREATE TABLE Email
(
EmailID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(EmailID),
EmailAddress VARCHAR(255)
);

CREATE TABLE Person
(
PersonID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(PersonID),
PersonCode VARCHAR(255),
PersonLastName VARCHAR(255),
PersonFirstName VARCHAR(255),
AddressID INT NOT NULL,
FOREIGN KEY `fk_Person_to_Address` (AddressID) REFERENCES Address(AddressID),
PersonPhone VARCHAR(255),
EmailID INT NOT NULL,
FOREIGN KEY `fk_Person_to_Email` (EmailID) REFERENCES Email(EmailID)
);

CREATE TABLE Airport
(
AirportID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(AirportID),
AirportCode VARCHAR(255),
AirportName VARCHAR(255),
AddressID INT NOT NULL,
FOREIGN KEY `fk_Airport_to_Address` (AddressID) REFERENCES Address(AddressID),
AirportLatDeg INT NOT NULL,
AirportLatMin INT NOT NULL,
AirportLongDeg INT NOT NULL,
AirportLongMin INT NOT NULL,
AirportPassFacilityFee FLOAT NOT NULL
);

CREATE TABLE Customer
(
CustomerID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(CustomerID),
CustomerCode VARCHAR(255),
CustomerType VARCHAR(255),
PrimaryContact INT NOT NULL,
FOREIGN KEY `fk_Customer_to_Person` (PrimaryContact) REFERENCES Person(PersonID),
CustomerName VARCHAR(255),
CustomerAirlineMiles FLOAT NOT NULL
);

CREATE TABLE Product
(
ProductID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(ProductID),
ProductCode VARCHAR(255),
ProductType VARCHAR(255),
DepAirportCode INT,
FOREIGN KEY `fk_Product_to_Airport` (DepAirportCode) REFERENCES Airport(AirportID),
ArrAirportCode INT,
FOREIGN KEY `frk_Product_to_Airport` (ArrAirportCode) REFERENCES Airport(AirportID),
DepartureTime VARCHAR(255),
ArrivalTime VARCHAR(255),
FlightNumber VARCHAR(255),
FlightClass VARCHAR(255),
AircraftType VARCHAR(255),
SeasonStartDate VARCHAR(255),
SeasonEndDate VARCHAR(255),
OffseasonRebate FLOAT,
AwardPointsPerMile FLOAT,
BaggageTicketCode INT,
FOREIGN KEY `fk_Product_to_Product` (BaggageTicketCode) REFERENCES Product(ProductID),
InsuranceName VARCHAR(255),
SpecialTypeOfService VARCHAR(255),
RefreshmentName VARCHAR(255),
RefreshmentCost FLOAT
);

CREATE TABLE Invoice
(
InvoiceID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(InvoiceID),
InvoiceCode VARCHAR(255),
CustomerCode INT NOT NULL,
FOREIGN KEY `fk_Invoice_to_Customer` (CustomerCode) REFERENCES Customer(CustomerID),
SalespersonCode INT NOT NULL,
FOREIGN KEY `fk_Invoice_to_Person` (SalespersonCode) REFERENCES Person(PersonID),
InvoiceDate VARCHAR(255)
);

CREATE TABLE ProductInvoice
(
ProductInvoiceID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(ProductInvoiceID),
ProductID INT NOT NULL,
FOREIGN KEY `fk_ProductInvoice_to_Product` (ProductID) REFERENCES Product(ProductID),
InvoiceID INT NOT NULL,
FOREIGN KEY `fk_ProductInvoice_to_Invoice`(InvoiceID) REFERENCES Invoice(InvoiceID),
TravelDate VARCHAR(255),
NumOfPassengers INT NOT NULL,
Seat VARCHAR(255),
TicketNote VARCHAR(255)
);

CREATE TABLE Passenger
(
PassengerID INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(PassengerID),
PersonID INT NOT NULL,
FOREIGN KEY `fk_Passenger_to_Person` (PersonID) REFERENCES Person(PersonID),
IdentityNumber VARCHAR(255),
PassengerAge INT NOT NULL,
PassengerNationality VARCHAR(255),
ProductInvoiceID INT NOT NULL,
FOREIGN KEY `fk_Passenger_to_ProductInvoice` (ProductInvoiceID) 
REFERENCES ProductInvoice(ProductInvoiceID)
);

这是我得到的错误:

Error Code: 1217. Cannot delete or update a parent row: a foreign key constraint fails 

并且它在发票的 DROP TABLE 上失败。

Answer 1

禁用外键检查或。删除整个数据库或。运行脚本两次或。首先删除 fk 约束。

如果您突出显示所有表并创建删除脚本，Mysql Workbench 将为您生成一个脚本。与 mysql 转储实用程序相同。

How to temporarily disable a foreign key constraint in MySQL?