db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <BBergqu...@canoga.com>
Subject RE: How to drop two interrelated tables at the same time?
Date Fri, 10 Feb 2012 00:22:03 GMT
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


Mime
View raw message