Cannot Drop Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Cannot Drop Tables

Author

Topic

kigroy
Starting Member

8 Posts

Posted - 2011-01-09 : 22:39:19

I'm about to lose my mind on this one...

I need to create tables and the drop them all. But for whatever reason when I drop them not all of them drop.

I posted all my code below. When I run the cleanup.sql not all tables are being deleted.

Any help would be greatly appreciated. I've lost half my day to this.

Thanks

This is my code for creating the tables and adding constraints:

--create tables
CREATE TABLE branch
(branchNo varchar(4) not null,
street varchar2(30),
city varchar(20),
postcode number(5) unique,
mgrStaffNo varchar(4),
PRIMARY KEY (branchNo));

CREATE TABLE telephone
(telNo char(13) not null,
branchNo varchar(4),
--foreign key (branchNo) references branch(branchNo),
primary key (telNo));

CREATE TABLE staff
(staffNo varchar(4) not null,
fName varchar(20),
lName varchar(30),
position varchar(20),
sex varchar(1),
DOB date,
salary number(8,2),
supervisorStaffNo varchar(4),
branchNo varchar(4),
--foreign key (supervisorStaffNo) references Staff(staffNo),
--foreign key (branchNo) references branch(branchNo),
primary key (staffNo));

CREATE TABLE manager
(staffNo varchar(4) not null,
mgrStartDate date,
bonus number,
--forgein key (branchNo) REFERENCES branch(branchNo)
PRIMARY KEY (staffNo));

CREATE TABLE privateOwner
(ownerNo varchar(4) not null,
fName varchar(20),
lName varchar(30),
address varchar(1000),
telNo varchar(13),
PRIMARY KEY (ownerNo));

CREATE TABLE businessOwner
(ownerNo varchar(4) not null,
bName varchar(20) unique,
bType varchar(60),
contactName varchar(60),
address varchar(1000),
telNo varchar(13) unique,
PRIMARY KEY (ownerNo));

CREATE TABLE propertyForRent
(propertyNo varchar(4) not null,
street varchar(1000),
city varchar(20),
postcode number(7),
type varchar(10),
rooms number,
rent number(7,2),
ownerNo varchar(4),
staffNo varchar(4),
branchNo varchar(4),
--FOREIGN KEY (ownerNo) REFERENCES privateOwner(ownerNo) AND businessOwner(ownerNo),
--FOREIGN KEY (staffNo) REFERENCES staff(staffNo),
--FOREIGN KEY (branchNo) REFERENCES branch(branchNo),
PRIMARY KEY (propertyNo));

CREATE TABLE viewing
(clientNo varchar(4) not null,
propertyNo varchar(4) not null,
dateView date,
comText varchar(20),
--FOREIGN KEY clientNo references clinet(clientNo),
--FOREIGN KEY propertyNo references PropertyForRent(propertyNo),*/
PRIMARY KEY (clientNo,propertyNo));

CREATE TABLE client
(clientNo varchar(4) not null,
fName varchar(10),
lName varchar(10),
telNo varchar(13),
eMail varchar(100) unique,
prefType varchar(10),
maxRent number(8,2),
PRIMARY KEY (clientNo));

CREATE TABLE registration
(clientNo varchar(4) not null,
branchNo varchar(4) not null,
staffNo varchar(4),
dateJoined date,
--FOREIGN KEY clientNo REFERENCES client(clientNo),
--FOREIGN KEY branchNo REFERENCES branch(branchNo),
--FOREING KEY staffNo REFERENCES staff(staffNo),
PRIMARY KEY (clientNo, branchNo));

CREATE TABLE lease
(leaseNo varchar(4) not null,
paymentMethod varchar(20),
depositPaid varchar(1),
rentStart DATE,
rentFinish DATE,
clientNo varchar(4),
propertyNo varchar(4),
deposit number(8,2),
duration number,
CONSTRAINT prop_rent UNIQUE (propertyNo, rentStart),
CONSTRAINT client_rent UNIQUE (clientNo, rentStart),
--FOREIGN KEY clientNo REFERENCES client(clientNo),
--FOREIGN KEY propertyNo REFERENCES propertyForRent(propertyNo),
PRIMARY KEY (leaseNo));

CREATE TABLE newspaper
(newspaperName varchar(60) not null,
address varchar(60),
telNo varchar(13) unique,
contactName varchar(60),
PRIMARY KEY (newspaperName));

CREATE TABLE advert
(propertyNo varchar(4) not null,
newspaperName varchar(100) not null,
dateAdvert date not null,
cost number(10,2),
--FOREIGN KEY propertyNo REFERENCES propertyForRent(propertyNo),
--FOREIGN KEY newspaperName REFERENCES newspaper(newspaperName),
PRIMARY KEY (propertyNo, newspaperName, dateAdvert));

--populate tables

INSERT INTO branch (branchNo, street, city, postcode, mgrStaffNo) VALUES ('B001', '111 Fish Point Rd', 'Prior Lake', 11111, 'S004');
INSERT INTO branch (branchNo, street, city, postcode, mgrStaffNo) VALUES ('B002', '222 Walleye St', 'Hackensack', 22222, 'S005');
INSERT INTO branch (branchNo, street, city, postcode, mgrStaffNo) VALUES ('B003', '333 Bass Ct', 'Faribault', 33333, 'S003');
INSERT INTO branch (branchNo, street, city, postcode, mgrStaffNo) VALUES ('B004', '444 Nothern Pike Rd', 'Long Lake', 44444, 'S004');
INSERT INTO branch (branchNo, street, city, postcode, mgrStaffNo) VALUES ('B005', '555 Sunfish Lane', 'Backus', 55555, 'S005');
INSERT INTO branch (branchNo, street, city, postcode, mgrStaffNo) VALUES ('B006', '666 Bullhead Blvd', 'Morris', 66666, 'S004');
INSERT INTO branch (branchNo, street, city, postcode, mgrStaffNo) VALUES ('B007', '777 Trout Trail', 'Pine River', 77777, 'S005');

INSERT INTO telephone (telNo, branchNo) VALUES ('111-111-1111', 'B001');
INSERT INTO telephone (telNo, branchNo) VALUES ('222-222-2222', 'B002');
INSERT INTO telephone (telNo, branchNo) VALUES ('333-333-3333', 'B003');
INSERT INTO telephone (telNo, branchNo) VALUES ('444-444-4444', 'B004');
INSERT INTO telephone (telNo, branchNo) VALUES ('555-555-5555', 'B005');
INSERT INTO telephone (telNo, branchNo) VALUES ('666-666-6666', 'B006');
INSERT INTO telephone (telNo, branchNo) VALUES ('777-777-7777', 'B007');

INSERT INTO staff (staffNo, fName, lName, position, sex, DOB, salary, supervisorStaffNo, branchNo) VALUES ('S001', 'Alex', 'Anderson', 'Advertiser', 'M', '01-JAN-1910', 10000, 'S004', 'B001');
INSERT INTO staff (staffNo, fName, lName, position, sex, DOB, salary, supervisorStaffNo, branchNo) VALUES ('S002', 'Betty', 'Boop', 'Bench Warmer', 'F', '01-JAN-1920', 20000, 'S005', 'B002');
INSERT INTO staff (staffNo, fName, lName, position, sex, DOB, salary, supervisorStaffNo, branchNo) VALUES ('S003', 'Carl', 'Candy', 'Cartographer', 'M', '01-JAN-1930', 30000, NULL, 'B003');
INSERT INTO staff (staffNo, fName, lName, position, sex, DOB, salary, supervisorStaffNo, branchNo) VALUES ('S004', 'Donna', 'Diggler', 'Developer', 'F', '01-JAN-1940', 40000, NULL, 'B001');
INSERT INTO staff (staffNo, fName, lName, position, sex, DOB, salary, supervisorStaffNo, branchNo) VALUES ('S005', 'Eddie', 'Edgarson', 'Estate Sales', 'M', '01-JAN-1950', 50000, NULL, 'B002');
INSERT INTO staff (staffNo, fName, lName, position, sex, DOB, salary, supervisorStaffNo, branchNo) VALUES ('S006', 'Franny', 'Fearless', 'Field Sales', 'F', '01-JAN-1960', 60000, NULL, 'B004');
INSERT INTO staff (staffNo, fName, lName, position, sex, DOB, salary, supervisorStaffNo, branchNo) VALUES ('S007', 'Gilbert', 'Goodheart', 'Gardener', 'M', '01-JAN-1970', 60000, NULL, 'B005');

INSERT INTO manager (staffNo, mgrStartDate, bonus) VALUES ('S001', '01-JAN-2001', 1000);
INSERT INTO manager (staffNo, mgrStartDate, bonus) VALUES ('S002', '01-JAN-2002', 2000);
INSERT INTO manager (staffNo, mgrStartDate, bonus) VALUES ('S003', '01-JAN-2003', 3000);
INSERT INTO manager (staffNo, mgrStartDate, bonus) VALUES ('S004', '01-JAN-2004', 4000);
INSERT INTO manager (staffNo, mgrStartDate, bonus) VALUES ('S005', '01-JAN-2005', 5000);
INSERT INTO manager (staffNo, mgrStartDate, bonus) VALUES ('S006', '01-JAN-2006', 6000);
INSERT INTO manager (staffNo, mgrStartDate, bonus) VALUES ('S007', '01-JAN-2007', 7000);

INSERT INTO privateOwner (ownerNo, fName, lName, address, telNo) VALUES ('O001', 'Owen', 'Addison', '1111 Hermit Trail, Prior Lake, MN, 11111', '111-122-2222');
INSERT INTO privateOwner (ownerNo, fName, lName, address, telNo) VALUES ('O002', 'Ownee', 'Babbit', '2222 Hermit Trail, Hackensack, MN, 22222', '222-122-2222');
INSERT INTO privateOwner (ownerNo, fName, lName, address, telNo) VALUES ('O003', 'Owenina', 'Clement', '3333 Hermit Trail, Faribault, MN, 33333', '333-122-2222');
INSERT INTO privateOwner (ownerNo, fName, lName, address, telNo) VALUES ('O004', 'Owenita', 'Davidson', '4444 Hermit Trail, Long Lake, MN, 44444', '444-122-2222');
INSERT INTO privateOwner (ownerNo, fName, lName, address, telNo) VALUES ('O005', 'Owendo', 'Earnhart', '5555 Hermit Trail, Backus, MN, 55555', '555-122-2222');
INSERT INTO privateOwner (ownerNo, fName, lName, address, telNo) VALUES ('O006', 'Owendy', 'Falcon', '6666 Hermit Trail, Morris, MN, 66666', '666-122-2222');
INSERT INTO privateOwner (ownerNo, fName, lName, address, telNo) VALUES ('O007', 'Owenda', 'Gnarls', '7777 Hermit Trail, Pine River, MN, 77777', '777-122-2222');

INSERT INTO businessOwner (ownerNo, bName, bType, contactName, address, telNo) VALUES ('O008', 'Antler Emporium', 'retail', 'Annie Antler', '11111 Snowberry Lane, Prior Lake, MN, 11111', '111-112-2222');
INSERT INTO businessOwner (ownerNo, bName, bType, contactName, address, telNo) VALUES ('O009', 'Bettys Breakfast', 'eatery', 'Betty Lane', '22222 Snowberry Lane, Hackensack, MN, 22222', '222-112-2222');
INSERT INTO businessOwner (ownerNo, bName, bType, contactName, address, telNo) VALUES ('O010', 'Clydes Bank', 'bank', 'Clydes Money', '33333 Snowberry Lane, Faribault, MN, 33333', '333-112-2222');
INSERT INTO businessOwner (ownerNo, bName, bType, contactName, address, telNo) VALUES ('O011', 'Dasiy Wharehose', 'retail', 'Daisy Flowers', '44444 Snowberry Lane, Long Lake, MN, 44444', '444-112-2222');
INSERT INTO businessOwner (ownerNo, bName, bType, contactName, address, telNo) VALUES ('O012', 'Edith Bank', 'bank', 'Edith Cents', '55555 Snowberry Lane, Backus, MN, 55555', '555-112-2222');
INSERT INTO businessOwner (ownerNo, bName, bType, contactName, address, telNo) VALUES ('O013', 'Faribault Foods', 'eatery', 'Fatih Foodson', '66666 Snowberry Lane, Morris, MN, 66666', '666-112-2222');
INSERT INTO businessOwner (ownerNo, bName, bType, contactName, address, telNo) VALUES ('O014', 'Georges River House', 'eatery', 'George Rivers', '77777 Snowberry Lane, Pine River, MN, 77777', '777-112-2222');

INSERT INTO propertyForRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo) VALUES ('P001', '111 Adison Street', 'Prior Lake', '11111', 'flat', 2, 1000, 'O001', 'S001', 'B001');
INSERT INTO propertyForRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo) VALUES ('P002', '112 Adison Street', 'Prior Lake', '11111', 'flat', 2, 1000, 'O002', 'S001', 'B001');
INSERT INTO propertyForRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo) VALUES ('P003', '111 Adison Street', 'Hackensack', '22222', 'house', 2, 1000, 'O003', 'S002', 'B002');
INSERT INTO propertyForRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo) VALUES ('P004', '112 Adison Street', 'Hackensack', '22222', 'flat', 3, 2000, 'O004', 'S002', 'B002');
INSERT INTO propertyForRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo) VALUES ('P005', '111 Adison Street', 'Faribault', '33333', 'flat', 3, 2000, 'O010', 'S003', 'B003');
INSERT INTO propertyForRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo) VALUES ('P006', '112 Adison Street', 'Faribault', '33333', 'house', 4, 3000, 'O010', 'S003', 'B003');
INSERT INTO propertyForRent (propertyNo, street, city, postcode, type, rooms, rent, ownerNo, staffNo, branchNo) VALUES ('P007', '113 Adison Street', 'Faribault', '33333', 'house', 4, 3000, 'O010', 'S003', 'B003');

INSERT INTO viewing (clientNo, propertyNo, dateView, comText) VALUES ('C001', 'P004', '01-JAN-2011', 'too small');
INSERT INTO viewing (clientNo, propertyNo, dateView, comText) VALUES ('C001', 'P005', '02-JAN-2011', 'too big');
INSERT INTO viewing (clientNo, propertyNo, dateView, comText) VALUES ('C001', 'P006', '03-JAN-2011', 'just right');
INSERT INTO viewing (clientNo, propertyNo, dateView, comText) VALUES ('C002', 'P004', '01-JAN-2011', 'too small');
INSERT INTO viewing (clientNo, propertyNo, dateView, comText) VALUES ('C002', 'P005', '02-JAN-2011', 'too big');
INSERT INTO viewing (clientNo, propertyNo, dateView, comText) VALUES ('C002', 'P006', '03-JAN-2011', 'too smelly');
INSERT INTO viewing (clientNo, propertyNo, dateView, comText) VALUES ('C002', 'P007', '04-JAN-2011', 'just right');

INSERT INTO client (clientNo, fName, lName, telNo, eMail, prefType, maxRent) VALUES ('C001', 'Alexander', 'Augusta', '111-222-3333', 'aagusta@email.com', 'flat', 2000.00);
INSERT INTO client (clientNo, fName, lName, telNo, eMail, prefType, maxRent) VALUES ('C002', 'Beatrice', 'Bonita', '111-222-3332', 'bbonita@email.com', 'flat', 3000.00);
INSERT INTO client (clientNo, fName, lName, telNo, eMail, prefType, maxRent) VALUES ('C003', 'Clare', 'Coletrain', '111-222-3322', 'ccoletrain@email.com', 'house', 1000.00);
INSERT INTO client (clientNo, fName, lName, telNo, eMail, prefType, maxRent) VALUES ('C004', 'David', 'Duchess', '111-222-3334', 'dduchess@email.com', 'flat', 4000.00);
INSERT INTO client (clientNo, fName, lName, telNo, eMail, prefType, maxRent) VALUES ('C005', 'Eleanor', 'Roosevelt', '111-222-3335', 'eroosevelt@email.com', 'house', 4000.00);
INSERT INTO client (clientNo, fName, lName, telNo, eMail, prefType, maxRent) VALUES ('C006', 'Freddi', 'Farquar', '111-222-3336', 'ffarquar@email.com', 'house', 4000.00);
INSERT INTO client (clientNo, fName, lName, telNo, eMail, prefType, maxRent) VALUES ('C007', 'Gill', 'Fishbottom', '111-222-3337', 'gfishbottom@email.com', 'flat', 2000.00);

INSERT INTO registration (clientNo, branchNo, staffNo, dateJoined) VALUES ('C001', 'B001', 'S001', '22-DEC-2009');
INSERT INTO registration (clientNo, branchNo, staffNo, dateJoined) VALUES ('C002', 'B001', 'S001', '22-DEC-2010');
INSERT INTO registration (clientNo, branchNo, staffNo, dateJoined) VALUES ('C003', 'B001', 'S001', '22-DEC-2007');
INSERT INTO registration (clientNo, branchNo, staffNo, dateJoined) VALUES ('C004', 'B001', 'S001', '22-JUN-2009');
INSERT INTO registration (clientNo, branchNo, staffNo, dateJoined) VALUES ('C005', 'B002', 'S002', '22-DEC-2007');
INSERT INTO registration (clientNo, branchNo, staffNo, dateJoined) VALUES ('C006', 'B002', 'S002', '22-DEC-2009');
INSERT INTO registration (clientNo, branchNo, staffNo, dateJoined) VALUES ('C007', 'B002', 'S002', '22-DEC-2010');

INSERT INTO lease (leaseNo, paymentMethod, depositPaid, rentStart, rentFinish, clientNo, propertyNo) VALUES ('L001', 'CASH', 'Y', '1-JAN-2009', '31-MAR-2009', 'C003', 'P001');
INSERT INTO lease (leaseNo, paymentMethod, depositPaid, rentStart, rentFinish, clientNo, propertyNo) VALUES ('L002', 'CREDIT', 'Y', '1-APR-2009', '30-JUN-2010', 'C003', 'P001');
INSERT INTO lease (leaseNo, paymentMethod, depositPaid, rentStart, rentFinish, clientNo, propertyNo) VALUES ('L003', 'CHECK', 'Y', '1-JUL-2009', '30-SEP-2009', 'C003', 'P001');
INSERT INTO lease (leaseNo, paymentMethod, depositPaid, rentStart, rentFinish, clientNo, propertyNo) VALUES ('L004', 'CASH', 'N', '1-OCT-2009', '31-DEC-2010', 'C003', 'P001');
INSERT INTO lease (leaseNo, paymentMethod, depositPaid, rentStart, rentFinish, clientNo, propertyNo) VALUES ('L005', 'CASH', 'N', '1-JAN-2010', '1-NOV-2010', 'C004', 'P002');
INSERT INTO lease (leaseNo, paymentMethod, depositPaid, rentStart, rentFinish, clientNo, propertyNo) VALUES ('L008', 'CHECK', 'Y', '1-APR-1985', '1-DEC-1985', 'C005', 'P003');
INSERT INTO lease (leaseNo, paymentMethod, depositPaid, rentStart, rentFinish, clientNo, propertyNo) VALUES ('L006', 'CHECK', 'Y', (SELECT ADD_MONTHS(SYSDATE,-1) FROM DUAL), (SELECT ADD_MONTHS(SYSDATE,1) FROM DUAL), 'C005', 'P003');
INSERT INTO lease (leaseNo, paymentMethod, depositPaid, rentStart, rentFinish, clientNo, propertyNo) VALUES ('L007', 'CASH', 'Y', '1-MAR-2009', '31-MAR-2010', 'C006', 'P003');
INSERT INTO lease (leaseNo, paymentMethod, depositPaid, rentStart, rentFinish, clientNo, propertyNo) VALUES ('L010', 'CASH', 'N', '1-JAN-2011', '1-MAR-2011', 'C004', 'P002');
INSERT INTO lease (leaseNo, paymentMethod, depositPaid, rentStart, rentFinish, clientNo, propertyNo) VALUES ('L009', 'CHECK', 'Y', '1-DEC-2010', '1-FEB-2011', 'C005', 'P003');

INSERT INTO newspaper (newsPaperName, address, telNo, contactName) VALUES ('Prior Lake Daily News', '111 News Rd Prior Lake MN 11111', '111-444-1111', 'Janice Newsworthy');
INSERT INTO newspaper (newsPaperName, address, telNo, contactName) VALUES ('Hackensak Daily News', '111 News Rd Hackensack MN 22222', '222-444-1111', 'Janet Newsworthy');
INSERT INTO newspaper (newsPaperName, address, telNo, contactName) VALUES ('Faribault Daily News', '111 News Rd Faribault MN 33333', '333-444-1111', 'Jade Newsworthy');
INSERT INTO newspaper (newsPaperName, address, telNo, contactName) VALUES ('Long Lake Daily News', '111 News Rd Long Lake MN 44444', '444-444-1111', 'Judy Newsworthy');
INSERT INTO newspaper (newsPaperName, address, telNo, contactName) VALUES ('Morris Daily News', '111 News Rd Morris MN 55555', '555-444-1111', 'Justine Newsworthy');
INSERT INTO newspaper (newsPaperName, address, telNo, contactName) VALUES ('Backus Daily News', '111 News Rd Backus MN 66666', '666-444-1111', 'Josephina Newsworthy');
INSERT INTO newspaper (newsPaperName, address, telNo, contactName) VALUES ('Pine River Daily News', '111 News Rd Pine River MN 77777', '777-444-1111', 'Janet Newsworthy');

INSERT INTO advert (propertyNo, newspaperName, dateAdvert, cost) VALUES ('P004', 'Prior Lake Daily News', '2-JAN-2011', 12.00);
INSERT INTO advert (propertyNo, newspaperName, dateAdvert, cost) VALUES ('P004', 'Faribault Daily News', '2-JAN-2011', 20.00);
INSERT INTO advert (propertyNo, newspaperName, dateAdvert, cost) VALUES ('P005', 'Prior Lake Daily News', '2-JAN-2011', 12.00);
INSERT INTO advert (propertyNo, newspaperName, dateAdvert, cost) VALUES ('P005', 'Faribault Daily News', '2-JAN-2011', 20.00);
INSERT INTO advert (propertyNo, newspaperName, dateAdvert, cost) VALUES ('P006', 'Prior Lake Daily News', '2-JAN-2011', 12.00);
INSERT INTO advert (propertyNo, newspaperName, dateAdvert, cost) VALUES ('P006', 'Faribault Daily News', '2-JAN-2011', 20.00);
INSERT INTO advert (propertyNo, newspaperName, dateAdvert, cost) VALUES ('P007', 'Prior Lake Daily News', '2-JAN-2011', 12.00);

UPDATE lease SET deposit = (SELECT rent*2 FROM propertyForRent WHERE propertyForRent.propertyNo = lease.propertyNo);

UPDATE lease SET duration = (rentFinish - rentStart);

--Add constraints
ALTER TABLE branch
ADD foreign key (mgrStaffNo) references Manager(staffNo) ON DELETE SET NULL;

ALTER TABLE telephone
ADD foreign key (branchNo) references branch(branchNo) ON DELETE CASCADE;

ALTER TABLE staff
ADD foreign key (supervisorStaffNo) references Staff(staffNo) ON DELETE SET NULL;

ALTER TABLE staff
ADD foreign key (branchNo) references branch(branchNo) ON DELETE CASCADE;

ALTER TABLE manager
ADD foreign key (staffNo) references staff(staffNo) ON DELETE SET NULL;

ALTER TABLE propertyForRent ADD FOREIGN KEY(OwnerNo) REFERENCES PrivateOwner(OwnerNo) NOVALIDATE;

ALTER TABLE propertyForRent ADD FOREIGN KEY(OwnerNo) REFERENCES businessOwner(OwnerNo) NOVALIDATE;

ALTER TABLE propertyForRent
ADD FOREIGN KEY (staffNo) REFERENCES staff(staffNo) ON DELETE CASCADE;

ALTER TABLE propertyForRent
ADD FOREIGN KEY (branchNo) REFERENCES branch(branchNo) ON DELETE CASCADE;

ALTER TABLE viewing
ADD FOREIGN KEY (clientNo) references client(clientNo) ON DELETE CASCADE;

ALTER TABLE viewing
ADD FOREIGN KEY (propertyNo) references PropertyForRent(propertyNo) ON DELETE CASCADE;

ALTER TABLE registration
ADD FOREIGN KEY (clientNo) REFERENCES client(clientNo) ON DELETE CASCADE;

ALTER TABLE registration
ADD FOREIGN KEY (branchNo) REFERENCES branch(branchNo) ON DELETE CASCADE;

ALTER TABLE registration
ADD FOREIGN KEY (staffNo) REFERENCES staff(staffNo) ON DELETE CASCADE;

ALTER TABLE lease
ADD FOREIGN KEY (clientNo) REFERENCES client(clientNo) ON DELETE CASCADE;

ALTER TABLE lease
ADD FOREIGN KEY (propertyNo) REFERENCES propertyForRent(propertyNo) ON DELETE CASCADE;

ALTER TABLE advert
ADD FOREIGN KEY (propertyNo) REFERENCES propertyForRent(propertyNo) ON DELETE CASCADE;

ALTER TABLE advert
ADD FOREIGN KEY (newspaperName) REFERENCES newspaper(newspaperName) ON DELETE CASCADE;

This is my code for dropping the tables (the cleanup.sql):

ALTER TABLE branch
DROP PRIMARY KEY;
ALTER TABLE branch
DROP FOREIGN KEY (mgrStaffNo);

drop trigger viewing;
drop trigger depositCalculator;
drop table branch;
drop table telephone;
drop table staff;
drop table manager;
drop table privateOwner;
drop table businessOwner;
drop table propertyForRent;
drop table viewing;
drop table client;
drop table registration;
drop table lease;
drop table newspaper;
drop table advert;

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-01-09 : 23:53:18

the syntax for dropping a constraint is
ALTER TABLE TABLENAME DROP CONSTRAINT CONSTRAINTNAME

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-01-10 : 00:03:27

if you are getting any error while deletion please post the error message

kigroy
Starting Member

8 Posts

Posted - 2011-01-10 : 00:42:49

quote:
Originally posted by ahmeds08

if you are getting any error while deletion please post the error message

Thanks for the help. Ultimately, the the solution that resolved my problem was to add "CASCADE CONSTRAINTS" to my drop tables statements.

Next time I will be sure to add the error messages! On to my next question...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources