db-derby-user mailing list archives

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

Mime
View raw message