How to DROP TABLE IF EXISTS ___; Works?

user_12203092 0
Viewed 344

I am trying to drop table if these exists just to get the tables to create and work correctly however when I try to run it after they are created I get an error that says "Can't drop table 'Painter' referenced by a foreign key constraint on table painting". I think it is an issue with the foreign keys in the tables, I haven't written SQL in a while sorry if it is hard to read.

DROP TABLE IF EXISTS PAINTER;
DROP TABLE IF EXISTS MUSEUM;
DROP TABLE IF EXISTS PAINTING;
DROP TABLE IF EXISTS CUSTOMER;
DROP TABLE IF EXISTS GALA_NIGHT;
DROP TABLE IF EXISTS INVITE;
DROP TABLE IF EXISTS MEMBER_SPECIALTY;
DROP TABLE IF EXISTS DOCENT;

CREATE TABLE PAINTER (
AName varchar(255) PRIMARY KEY,
ABDate varchar(255),
ADdate varchar(255),
ACountry varchar(255)
);

CREATE TABLE MUSEUM (
MuseumName varchar(255) PRIMARY KEY,
MuCity varchar(255),
MuCountry varchar(255)
);

CREATE TABLE PAINTING (
Painting_Name varchar(255) PRIMARY KEY,
Year_Painted varchar(255),
Est_Value varchar(255),
PAINTER_Name varchar(255),
FOREIGN KEY (PAINTER_Name) REFERENCES PAINTER(AName),
Museum_Name varchar(255),
FOREIGN KEY (Museum_Name) REFERENCES MUSEUM(MuseumName)
);

CREATE TABLE CUSTOMER (
CustId int(255) PRIMARY KEY,
CustName varchar(255),
CustBDate varchar(255),
CustType varchar(255)
);

CREATE TABLE GALA_NIGHT (
Painter_Name varchar(255),
FOREIGN KEY (Painter_Name) REFERENCES PAINTER(AName),
GalaDate varchar(255) PRIMARY KEY
);

CREATE TABLE INVITE (
Painter_Name varchar(255) PRIMARY KEY,
FOREIGN KEY (Painter_Name) REFERENCES GALA_NIGHT(Painter_Name),
GalaDate varchar(255),
FOREIGN KEY (GalaDate) REFERENCES GALA_NIGHT(GalaDate),
Cust_Id int(255),
FOREIGN KEY (Cust_Id) REFERENCES CUSTOMER(CustId)
);

CREATE TABLE MEMBER_SPECIALTY (
memberType varchar(255),
FOREIGN KEY (memberType) REFERENCES CUSTOMER(CustType)
);

CREATE TABLE DOCENT (
DDate varchar(255) PRIMARY KEY,
Dtime varchar(255),
Room_Number int(255),
CustID varchar(255),
FOREIGN KEY (CustID) REFERENCES CUSTOMER(CustId)
);
mysql sql
2 Answers

In order to drop the PAINTER table, you need to remove the FK constraints that depend on that table. You could do that with an ALTER statement, but since you're dropping the PAINTING table anyway, you can put that command before command to drop PAINTER.

You have several dependencies among your tables. You'll probably need to re-order your DROP commands so they're in the reverse order of how you create the tables and constraints.

Bill_the_Lizard_12880

In mysql 8 it works , but you have some errors in your code

DROP TABLE IF EXISTS DOCENT;
DROP TABLE IF EXISTS MEMBER_SPECIALTY;
DROP TABLE IF EXISTS INVITE;
DROP TABLE IF EXISTS GALA_NIGHT;
DROP TABLE IF EXISTS CUSTOMER;
DROP TABLE IF EXISTS PAINTING;
DROP TABLE IF EXISTS MUSEUM;
DROP TABLE IF EXISTS PAINTER;

CREATE TABLE PAINTER (
AName varchar(255) PRIMARY KEY,
ABDate varchar(255),
ADdate varchar(255),
ACountry varchar(255)
);

CREATE TABLE MUSEUM (
MuseumName varchar(255) PRIMARY KEY,
MuCity varchar(255),
MuCountry varchar(255)
);

CREATE TABLE PAINTING (
Painting_Name varchar(255) PRIMARY KEY,
Year_Painted varchar(255),
Est_Value varchar(255),
PAINTER_Name varchar(255),
FOREIGN KEY (PAINTER_Name) REFERENCES PAINTER(AName),
Museum_Name varchar(255),
FOREIGN KEY (Museum_Name) REFERENCES MUSEUM(MuseumName)
);

CREATE TABLE CUSTOMER (
CustId int(255) PRIMARY KEY,
CustName varchar(255),
CustBDate varchar(255),
CustType varchar(255)
, KEY(CustType)
);

CREATE TABLE GALA_NIGHT (
Painter_Name varchar(255),
FOREIGN KEY (Painter_Name) REFERENCES PAINTER(AName),
GalaDate varchar(255) PRIMARY KEY
);

CREATE TABLE INVITE (
Painter_Name varchar(255) PRIMARY KEY,
FOREIGN KEY (Painter_Name) REFERENCES GALA_NIGHT(Painter_Name),
GalaDate varchar(255),
FOREIGN KEY (GalaDate) REFERENCES GALA_NIGHT(GalaDate),
Cust_Id int(255),
FOREIGN KEY (Cust_Id) REFERENCES CUSTOMER(CustId)
);

CREATE TABLE MEMBER_SPECIALTY (
memberType varchar(255),
FOREIGN KEY (memberType) REFERENCES CUSTOMER(CustType)
);

CREATE TABLE DOCENT (
DDate varchar(255) PRIMARY KEY,
Dtime varchar(255),
Room_Number int(255),
CustID int(255),
FOREIGN KEY (CustID) REFERENCES CUSTOMER(CustId)
);
user_51935360
Related