dropping tables in sql

Question

Hey so I am very new to SQL and am having trouble dropping tables, the SQL I have so far is:

CREATE TABLE COMPANY (
cname       VARCHAR(50)     NOT NULL,
city        VARCHAR(50)     NOT NULL,       
street      VARCHAR(50)     NOT NULL,
bldgnum     DECIMAL(4)      NOT NULL,
email       VARCHAR(320)    NOT NULL,

CONSTRAINT comp_pkey PRIMARY KEY(cname),
CONSTRAINT comp_ckey1 UNIQUE(city, street, bldgnum),
CONSTRAINT comp_ckey2 UNIQUE(email) );


CREATE TABLE DEPARTMENT (
cname       VARCHAR(50)     NOT NULL,
dname       VARCHAR(50)     NOT NULL,

CONSTRAINT dept_pkey PRIMARY KEY(cname, dname),
CONSTRAINT dept_fkey FOREIGN KEY(cname) REFERENCES COMPANY(cname) );


CREATE TABLE EMPLOYEE (
enumber             VARCHAR(50)     NOT NULL,
first_name          VARCHAR(50)     NOT NULL,   
last_name           VARCHAR(50)     NOT NULL,
date_of_brith       DATE                NULL,
salary              DECIMAL(7,2)    NOT NULL,
cname               VARCHAR(50)     NOT NULL,
dname               VARCHAR(50)     NOT NULL,
employee_type       VARCHAR(50)         NULL,
manager_of_cname    VARCHAR(50)         NULL,
manager_of_dname    VARCHAR(50)         NULL,

CONSTRAINT emp_pkey PRIMARY KEY(enumber),
CONSTRAINT emp_fkey FOREIGN(cname, dname) REFERENCES DEPARTMENT(cname, dname),
CONSTRAINT salary CHECK (salary < 0) );


CREATE TABLE PHONE (
pnumber         DECIMAL(10)         NOT NULL,
cname           VARCHAR(50)         NOT NULL,

CONSTRAINT phn_pkey PRIMARY KEY(pnumber),
CONSTRAINT phn_fkey FOREIGN KEY(cname) REFERENCES COMPANY(cname) );

This has some errors in it I believe but I want to drop these tables but get an error saying: Cannot delete or update a parent row: foreign key constraint fails. This is my drop table SQL:

DROP TABLE COMPANY;
DROP TABLE DEPARTMENT;
DROP TABLE EMPLOYEE;
DROP TABLE PHONE;

Sorry for any facepalms created while reading this, and thank you for any help as it is very appreciated.

Use DROP CONSTRAINT to remove the constraints, the you can drop the tables. — Sloan Thrasher, Apr 08 '17 at 02:57
also which database application are you using? you shouldn't be tagging both mysql and sql-server, you are likely using one or the other. — Niagaradad, Apr 08 '17 at 02:59

score 0 · Answer 1 · answered Apr 08 '17 at 03:03

You need to start with dropping the child tables. Here is the hierarcy you have:

Company --> Department --> Employee

You need to first drop the Employee table, then the Department and then the Company table. The point is that, SQL Server cannot drop a table while that table is being referenced by another table.

DROP TABLE EMPLOYEE;
DROP TABLE DEPARTMENT;
DROP TABLE COMPANY;

score 0 · Answer 2 · edited May 23 '17 at 10:30

0

On the employee table creation you missed the foreign key change this.

Cannot delete or update a parent row: a foreign key constraint fails ->> check with this link you find the solution

edited May 23 '17 at 10:30

Community

1
1

answered Apr 08 '17 at 03:38

neevan

31
8

dropping tables in sql

2 Answers2