db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Libor Jelinek <ljeli...@virtage.com>
Subject How to drop two interrelated tables at the same time?
Date Thu, 09 Feb 2012 23:44:53 GMT
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

Mime
View raw message