0

I'm getting this error, and I know it's coming from the DROP TABLE IF statements, but I don't know how else to organize the drops for it to work. I've tried rearranging them but it won't work. The problem came when I introduced the Passenger table but I don't know how to fix it.

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

This is the error I get:

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

and it fails on the DROP TABLE for Invoice.

Ryan Dorman
  • 317
  • 1
  • 3
  • 10
  • Um, `CREATE TABLE ProudctInvoice` is being referenced as `ProductInvoice` -- misspelling perhaps ? – sgeddes Oct 27 '15 at 03:42
  • 1
    That's because INVOICE refers CUSTOMER. Now the child is CUSTOMER, whereas INVOICE is the parent. Always drop the child table first and then try to drop the parent. – Sarath Chandra Oct 27 '15 at 03:46
  • @SarathChandra I tried to put CUSTOMER before INVOICE in the drop list and leave the rest the same but I still get the error. – Ryan Dorman Oct 27 '15 at 03:48
  • @RyanDorman Did you try putting the 'DROP IF EXISTS' statements right before each corresponding table? For eg: DROP TABLE IF EXISTS `Address`; CREATE TABLE ADDRESS...; DROP TABLE IF EXISTS `EMAIL`; CREATE TABLE EMAIL... and so on – Sarath Chandra Oct 27 '15 at 03:57
  • Does this help? http://stackoverflow.com/questions/3334619/cannot-delete-or-update-a-parent-row-a-foreign-key-constraint-fails – sgeddes Oct 27 '15 at 03:59
  • @SarathChandra Yes, I tried that but it gives me a similar error that says it can not drop it due to foreign key constraints. – Ryan Dorman Oct 27 '15 at 04:06

1 Answers1

0

Disable foreign key checks or. Drop the whole database or. Run the script twice or. Drop the fk constraints first.

Mysql workbench will generate a script for you if you highlight all if the tables and create a drop script. Same with the mysql dump utility.

How to temporarily disable a foreign key constraint in MySQL?

Community
  • 1
  • 1
Bob
  • 780
  • 9
  • 10