Error code 1217 in MySQL code

Question

I'm getting the error: Error 1217 in my MySQL code. My MySQL code is

DROP TABLE IF EXISTS Formed;
DROP TABLE IF EXISTS Album;
DROP TABLE IF EXISTS Band;
DROP TABLE IF EXISTS Customers;
DROP TABLE IF EXISTS Track;

CREATE TABLE Formed(
    FormedID int AUTO_INCREMENT, 
    YearFormed int, 
    CountryFormed varchar(50), 
    CityFormed varchar(50), 
    BandMembers varchar(400), 
    PRIMARY KEY(FormedID))
    ENGINE=InnoDB;

CREATE TABLE Track (
    TrackID int AUTO_INCREMENT, 
    AlbumID int AUTO_INCREMENT,
    Songs varchar (100), 
    TrackNumber varchar (20), 
    Title varchar (30), 
    TrackDuration varchar (4), 
    PRIMARY KEY (TrackID)) 
    ENGINE=InnoDB;

CREATE TABLE Album(
    AlbumID int AUTO_INCREMENT,
    TrackID int AUTO_INCREMENT,
    BandID int AUTO_INCREMENT,
    KEY(TrackID),
    KEY(BandID),
    Price varchar(5), 
    PublicationDate varchar(11), 
    Title varchar(30), 
    Genre varchar (36),
    PRIMARY KEY(AlbumID))
    ENGINE=InnoDB;

CREATE TABLE Band(
    BandID int AUTO_INCREMENT, 
    AlbumID int AUTO_INCREMENT, 
    KEY(AlbumID),
    RecordLabel varchar(50), 
    PRIMARY KEY(BandID))
    ENGINE=InnoDB;

CREATE TABLE Customers (
    CustomerID int AUTO_INCREMENT, 
    CName varchar (20), 
    CPhone int (11), 
    CEmail varchar (50), 
    CPPaid varchar (50), 
    CPDate date, 
    PRIMARY KEY (CustomerID))
    ENGINE=InnoDB;

ALTER TABLE Track 
ADD FOREIGN KEY (AlbumID) REFERENCES Album(AlbumID)ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE Album
ADD FOREIGN KEY (TrackID) REFERENCES Track(TrackID)ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE Album
ADD FOREIGN KEY (BandID) REFERENCES Band(BandID)ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE Band
ADD FOREIGN KEY (AlbumID) REFERENCES Album(AlbumID)ON DELETE SET NULL ON UPDATE CASCADE;

And I'm getting the error DROP TABLE IF EXISTS Album Error Code: 1217. Cannot delete or update a parent row: a foreign key constraint fails, it also happens for the other DROP TABLE IF EXISTS lines. I'm new to using Foreign Keys in MySQL scripts and I don't know why I'm getting these errors. If anyone could help me fix this, it would be appreciated. I've already tried all the answers that can be found by following this link Bogus foreign key constraint fail

You may have to drop them in a different order to avoid violating foreign key constraints, remove the foreign keys first, or even disable the foreign keys. — tadman, Apr 14 '16 at 16:59
@tadman I've just commented them out of the code and it's still happening — smitthy, Apr 14 '16 at 17:01
@Siguza It's not a duplication as I've tried all the answers given there and it still doesn't work — smitthy, Apr 14 '16 at 17:06

score 4 · Answer 1 · answered Apr 14 '16 at 17:57

4

Your Script is OK, but before Dropping Tables, try to remove the Foreign Key References

answered Apr 14 '16 at 17:57

Feelbad Soussi Wolfgun DZ

75
1
3

So it would be something along the lines of: `ALTER TABLE Track ADD FOREIGN KEY (AlbumID);`? – smitthy Apr 14 '16 at 18:07
Are you using phpmyadmin or workbench? Or Consol!? – Feelbad Soussi Wolfgun DZ Apr 14 '16 at 18:11
I'm using MySQL Workbench – smitthy Apr 14 '16 at 18:28
4

Before the drop queries put this SET FOREIGN_KEY_CHECKS=0 – Feelbad Soussi Wolfgun DZ Apr 14 '16 at 18:31
1

And before creating tables queries put this SET FOREIGN_KEY_CHECKS=1; – Feelbad Soussi Wolfgun DZ Apr 14 '16 at 18:33
Thanks, managed to get it to work – smitthy Apr 14 '16 at 19:31

Error code 1217 in MySQL code

1 Answers1