How to drop two interrelated tables at the same time?

[Libor Jelinek]

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

RE: How to drop two interrelated tables at the same time?

[Bergquist, Brett]

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 [ljeli...@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

Re: How to drop two interrelated tables at the same time?

[Libor Jelinek]

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

But thanks a lot despite!
Libor


2012/2/10 Bergquist, Brett 

> 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 [ljeli...@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
>
>

Re: How to drop two interrelated tables at the same time?

[John English]

On 10/02/2012 02:22, Bergquist, Brett wrote:

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


As a matter of interest, how would you ever insert data into these two
tables? Inserting into either one would violate the FK constraint in
the other, unless the FK allowed nulls -- in which case you do an insert
with a null key into one table, an insert with non-null into the other,
and then an update of the first table's FK. Or am I missing something?


 John English | My old University of Brighton home page is still here:
  | http://www.cem.brighton.ac.uk/staff/je/

Re: How to drop two interrelated tables at the same time?

[Libor Jelinek]

Right, John. Because these two tables reference each other with FK
constraints thus one column must allow null. Then it's application logic's
responsibility to ensure that immediately when referenced row from second
table is created it is set in first table (i.e. not leaving it null).

By the way are there some methodologies in response to this "Columbus's
egg" problem?

Thank you for your helpful responses!
Libor

2012/2/10 John English 

> On 10/02/2012 02:22, Bergquist, Brett wrote:
>
>> 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
>>
>
> As a matter of interest, how would you ever insert data into these two
> tables? Inserting into either one would violate the FK constraint in
> the other, unless the FK allowed nulls -- in which case you do an insert
> with a null key into one table, an insert with non-null into the other,
> and then an update of the first table's FK. Or am I missing something?
>
> --**--**
> 
>  John English | My old University of Brighton home page is still here:
>  | 
> http://www.cem.brighton.ac.uk/**staff/je/
> --**--**
> 
>

Re: How to drop two interrelated tables at the same time?

[Libor Jelinek]

I am sorry I was corrected by friend of mine that it's Aristotle's idea,
not Columbus's (also known as "chicken or the egg") :-)

Thanks
Libor

2012/2/10 Libor Jelinek 

> Right, John. Because these two tables reference each other with FK
> constraints thus one column must allow null. Then it's application logic's
> responsibility to ensure that immediately when referenced row from second
> table is created it is set in first table (i.e. not leaving it null).
>
> By the way are there some methodologies in response to this "Columbus's
> egg" problem?
>
> Thank you for your helpful responses!
> Libor
>
> 2012/2/10 John English 
>
>> On 10/02/2012 02:22, Bergquist, Brett wrote:
>>
>>> 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
>>>
>>
>> As a matter of interest, how would you ever insert data into these two
>> tables? Inserting into either one would violate the FK constraint in
>> the other, unless the FK allowed nulls -- in which case you do an insert
>> with a null key into one table, an insert with non-null into the other,
>> and then an update of the first table's FK. Or am I missing something?
>>
>> --**--**
>> 
>>  John English | My old University of Brighton home page is still here:
>>  | 
>> http://www.cem.brighton.ac.uk/**staff/je/
>> --**--**
>> 
>>
>
>

Re: How to drop two interrelated tables at the same time?

[John English]

On 10/02/2012 14:49, Libor Jelinek wrote:

Right, John. Because these two tables reference each other with FK
constraints thus one column must allow null. Then it's application
logic's responsibility to ensure that immediately when referenced row
from second table is created it is set in first table (i.e. not leaving
it null).


OK. I just seem to remember that some DBMSs allow a single insert to
multiple tables, or inserts to views, which *might* overcome this.

OTOH, if you're trying to achieve a strict 1:1 relation, couldn't you
merge the two tables into one (i.e. instead of table T1 having columns
A, B, C where A is the PK and B is FK to T2, and T2 has B, A, D, have
T with A,B,C,D)? Or again, am I missing something?


 John English | My old University of Brighton home page is still here:
  | http://www.cem.brighton.ac.uk/staff/je/