Use alter table business_objects drop constraint fk_created_by;; alter table users drop constraint fk_boid; drop table business objects; drop table users;
Basically reverse what you did to create the tables. Brett ________________________________________ From: Libor Jelinek [[email protected]] Sent: Thursday, February 09, 2012 6:44 PM To: derby-user Subject: How to drop two interrelated tables at the same time? Hello dear Derby community! Very stupid question but as I newcomer from MySQL (MyISAM) I am wondering how to delete table that has references to another table? Table business_objects refers to table users. Vice versa table users referes to business_objects. create table business_objects ( boid int not null generated always as identity, constraint pk_boid primary key (boid) ); create table users ( username varchar(60) not null, boid int not null, constraint pk_username primary key (username) ); alter table business_objects add constraint fk_created_by foreign key (created_by) references users (username); alter table users add constraint fk_boid foreign key (boid) references business_objects (boid); Dropping table users as first is denied because of existing reference from table business_objects: An error occurred when executing the SQL command: drop table users Operation 'DROP CONSTRAINT' cannot be performed on object 'PK_USERNAME' because CONSTRAINT 'FK_CREATED_BY' is dependent on that object. [SQL State=X0Y25, DB Errorcode=-1] But dropping table businness as first is also denied because of existing reference from table users with similar error. So only option is delete these interrelated tables at the same time -- but how? I tried to drop them in transaction but still the same as above. Thanks a lot! Libor
