No other way? Something like PostgreSQL's "DROP TABLE yx CASCADE"?

But thanks a lot despite!
Libor


2012/2/10 Bergquist, Brett <BBergquist@canoga.com>
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 [ljelinek@virtage.com]
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