Re: How to drop two interrelated tables at the same time?
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/
Re: How to drop two interrelated tables at the same time?
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?
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 john.fore...@gmail.com 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/http://www.cem.brighton.ac.uk/staff/je/ --**--**
Re: How to drop two interrelated tables at the same time?
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 ljeli...@virtage.com 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 john.fore...@gmail.com 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/http://www.cem.brighton.ac.uk/staff/je/ --**--**
RE: How to drop two interrelated tables at the same time?
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?
No other way? Something like PostgreSQL's DROP TABLE yx CASCADE? But thanks a lot despite! Libor 2012/2/10 Bergquist, Brett bbergqu...@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 [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
