Return-Path: X-Original-To: apmail-db-derby-user-archive@www.apache.org Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 6982192BD for ; Thu, 9 Feb 2012 23:45:53 +0000 (UTC) Received: (qmail 62178 invoked by uid 500); 9 Feb 2012 23:45:53 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 62088 invoked by uid 500); 9 Feb 2012 23:45:52 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 62081 invoked by uid 99); 9 Feb 2012 23:45:52 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 09 Feb 2012 23:45:52 +0000 X-ASF-Spam-Status: No, hits=2.4 required=5.0 tests=HTML_FONT_FACE_BAD,HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_NEUTRAL X-Spam-Check-By: apache.org Received-SPF: neutral (nike.apache.org: local policy) Received: from [209.85.216.172] (HELO mail-qy0-f172.google.com) (209.85.216.172) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 09 Feb 2012 23:45:44 +0000 Received: by qcsg13 with SMTP id g13so1478393qcs.31 for ; Thu, 09 Feb 2012 15:45:23 -0800 (PST) Received: by 10.229.136.82 with SMTP id q18mr2354620qct.139.1328831123221; Thu, 09 Feb 2012 15:45:23 -0800 (PST) MIME-Version: 1.0 Received: by 10.229.98.198 with HTTP; Thu, 9 Feb 2012 15:44:53 -0800 (PST) From: Libor Jelinek Date: Fri, 10 Feb 2012 00:44:53 +0100 Message-ID: Subject: How to drop two interrelated tables at the same time? To: derby-user Content-Type: multipart/alternative; boundary=00248c76913610ef0804b8909bba X-Gm-Message-State: ALoCoQnMj8tZUIlJVP0YNe0G1jmtli3Hp/BU7HlA8jvwPPIlYju6zsPVWz8aEQpX/q3AlZWC9+Xl X-Virus-Checked: Checked by ClamAV on apache.org --00248c76913610ef0804b8909bba Content-Type: text/plain; charset=UTF-8 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 --00248c76913610ef0804b8909bba Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hello dear Derby community!
Very stupid question but as I ne= wcomer from MySQL (MyISAM) I am wondering how to delete table that has refe= rences to another table?=C2=A0

Table business_o= bjects refers to table users. Vice versa table users refe= res to business_objects.

create = table business_objects (
boid <= /span>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 (userna= me)
);

alter table business_objects add c= onstraint fk_created_by
foreign key (created_b= y) references users (username);

alter table users add constraint fk_boid
foreign key (boid) ref= erences business_objects (boid);

Dropping t= able users=C2=A0as first=C2=A0is denied because of existing referenc= e from table business_objects:

An= error occurred when executing the SQL command:
=C2=A0 =C2=A0drop table users<= /div>
Operation 'DROP CONSTRA= INT' cannot be performed on object 'PK_USERNAME' because CONSTR= AINT 'FK_CREATED_BY' is dependent on that object. [SQL State=3DX0Y2= 5, DB Errorcode=3D-1]=C2=A0

But dropping table=C2=A0businness as first= =C2=A0is also denied because of existing reference from table users with si= milar error.

So only option is delete these int= errelated tables at the same time -- but how? I tried to drop them in t= ransaction but still the same as above.

Thanks a lot!
Libor
--00248c76913610ef0804b8909bba--