Return-Path: Delivered-To: apmail-db-jdo-dev-archive@www.apache.org Received: (qmail 86412 invoked from network); 30 Jan 2006 15:26:07 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 30 Jan 2006 15:26:07 -0000 Received: (qmail 29461 invoked by uid 500); 30 Jan 2006 15:26:03 -0000 Mailing-List: contact jdo-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: jdo-dev@db.apache.org Delivered-To: mailing list jdo-dev@db.apache.org Received: (qmail 29450 invoked by uid 99); 30 Jan 2006 15:26:03 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 30 Jan 2006 07:26:03 -0800 X-ASF-Spam-Status: No, hits=0.6 required=10.0 tests=UPPERCASE_50_75 X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: local policy) Received: from [212.224.30.66] (HELO service-01.spree.de) (212.224.30.66) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 30 Jan 2006 07:26:02 -0800 Received: from [172.16.1.19] (rio.spree.de [172.16.1.19]) (authenticated bits=0) by service-01.spree.de (8.13.4/8.13.4/Debian-3) with ESMTP id k0UFMwv0010910 for ; Mon, 30 Jan 2006 16:22:58 +0100 Message-ID: <43DE300B.4070901@spree.de> Date: Mon, 30 Jan 2006 16:26:03 +0100 From: Michael Watzek Organization: Tech@Spree GmbH User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: jdo-dev@db.apache.org Subject: Re: Dependent and element-dependent References: <20060127161329.497A810FB004@asf.osuosl.org> <43DA4D99.5050804@spree.de> <43DA50CC.4020203@spree.de> <138E155A-8F1B-4AD2-BB18-454F6CC1083C@Sun.COM> In-Reply-To: <138E155A-8F1B-4AD2-BB18-454F6CC1083C@Sun.COM> Content-Type: multipart/mixed; boundary="------------050706000505030801030703" X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N --------------050706000505030801030703 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hi Michelle, the attached patch is meant as a suggestion for named FKs in the schema. It changes schema1 and schema4 for datastore identity both. The idea is to replace all REFERENCES clauses in column definitions by CONSTRAINT clauses. Furthermore, it also replaces UNIQUE clauses in column definitions by CONSTRAINT clauses. Regards, Michael Craig L Russell wrote: > FYI, at the Friday JDO TCK conference call, Michelle volunteered to > update the schema with user-decipherable foreign key names. > > Craig > > On Jan 27, 2006, at 8:56 AM, Michael Watzek wrote: > >> Hi Andy, >> >> >>>> Do you have some info regarding what this FK is ? >>>> Sadly I've no idea what Derby's nice message >>>> "constraint 'SQL060127010242262' for key (3)" >>>> means. It might have been nice for it to provide a column name ? or >>>> a related table ? but no we get the result of '"SQL" + random ()' >>>> perhaps >>>> >>> It seems that the FK name is generated by Derby. I checked the FK >>> definitions in schema 1: There are several unnamed FKs in the schema >>> referencing table 'persons' which may be candidates for the message >>> above: >>> - persons.manager >>> - persons.mentor >>> - persons.hradvisor >>> - insuranceplans.employee >>> - employee_phoneno_type.empid >> >> I renamed the FKs above in my local schema. It appears that the >> problem occurs on the manager column. Please see below. >> >> Regards, >> Michael >> >> [java] ERROR 23503: DELETE on table 'PERSONS' caused a violation >> of foreign key constraint 'EMPS_MANAGER' for key (3 >> ). The statement has been rolled back. >> [java] at >> org.apache.derby.iapi.error.StandardException.newException(Unknown >> Source) >> [java] at >> org.apache.derby.impl.sql.execute.ReferencedKeyRIChecker.doCheck >> (Unknown Source) >> [java] at >> org.apache.derby.impl.sql.execute.RISetChecker.doPKCheck(Unknown Source) >> [java] at >> org.apache.derby.impl.sql.execute.DeleteResultSet.collectAffectedRows( >> Unknown Source) >> [java] at >> org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source) >> [java] at >> org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown >> Source) >> [java] at >> org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown >> Source) >> [java] at >> org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement >> (Unknown Source) >> [java] at >> org.apache.derby.impl.jdbc.EmbedPreparedStatement.execute(Unknown >> Source) >> [java] at >> com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.execute >> (NewProxyPreparedStatement.java:911) >> [java] at >> org.jpox.store.rdbms.request.Request.executeUpdate(Request.java:69) >> [java] at org.jpox.store.rdbms.request.DeleteRequest.execute >> (DeleteRequest.java:239) >> [java] at org.jpox.store.rdbms.table.ClassTable.delete >> (ClassTable.java:2170) >> [java] at org.jpox.store.rdbms.table.ClassTable.delete >> (ClassTable.java:2175) >> [java] at org.jpox.store.rdbms.table.ClassTable.delete >> (ClassTable.java:2175) >> [java] at org.jpox.store.StoreManager.delete >> (StoreManager.java:794) >> [java] at >> org.jpox.state.StateManagerImpl.internalDeletePersistent >> (StateManagerImpl.java:4010) >> [java] at org.jpox.state.StateManagerImpl.deletePersistent >> (StateManagerImpl.java:3966) >> [java] at >> org.jpox.AbstractPersistenceManager.internalDeletePersistent >> (AbstractPersistenceManager.java:1342) >> [java] at >> org.jpox.AbstractPersistenceManager.deletePersistentAll >> (AbstractPersistenceManager.java:1380) >> [java] at org.apache.jdo.tck.JDO_Test.deleteTearDownClasses >> (JDO_Test.java:396) >> [java] at org.apache.jdo.tck.JDO_Test.localTearDown >> (JDO_Test.java:318) >> [java] at org.apache.jdo.tck.JDO_Test.tearDown >> (JDO_Test.java:287) >> [java] at org.apache.jdo.tck.JDO_Test.runBare >> (JDO_Test.java:234) >> [java] at org.apache.jdo.tck.util.BatchTestRunner.start >> (BatchTestRunner.java:120) >> [java] at org.apache.jdo.tck.util.BatchTestRunner.main >> (BatchTestRunner.java:95) >> >> -- >> ------------------------------------------------------------------- >> Michael Watzek Tech@Spree Engineering GmbH >> mailto:mwa.tech@spree.de Buelowstr. 66 >> Tel.: ++49/30/235 520 36 10783 Berlin - Germany >> Fax.: ++49/30/217 520 12 http://www.spree.de/ >> ------------------------------------------------------------------- > > > Craig Russell > Architect, Sun Java Enterprise System http://java.sun.com/products/jdo > 408 276-5638 mailto:Craig.Russell@sun.com > P.S. A good JDO? O, Gasp! > -- ------------------------------------------------------------------- Michael Watzek Tech@Spree Engineering GmbH mailto:mwa.tech@spree.de Buelowstr. 66 Tel.: ++49/30/235 520 36 10783 Berlin - Germany Fax.: ++49/30/217 520 12 http://www.spree.de/ ------------------------------------------------------------------- --------------050706000505030801030703 Content-Type: text/plain; name="Schema1Schema4.patch" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="Schema1Schema4.patch" Index: src/sql/derby/datastoreidentity/schema1.sql =================================================================== --- src/sql/derby/datastoreidentity/schema1.sql (revision 372795) +++ src/sql/derby/datastoreidentity/schema1.sql (working copy) @@ -51,13 +51,14 @@ ID INTEGER, NAME VARCHAR(32) NOT NULL, EMP_OF_THE_MONTH INTEGER, - COMPANYID INTEGER REFERENCES companies, - CONSTRAINT DEPTS_PK PRIMARY KEY (DATASTORE_IDENTITY) + COMPANYID INTEGER, + CONSTRAINT DEPTS_PK PRIMARY KEY (DATASTORE_IDENTITY), + CONSTRAINT DEPTS_COMPANYID FOREIGN KEY (COMPANYID) REFERENCES companies (DATASTORE_IDENTITY) ); CREATE TABLE persons ( DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, - PERSONID INTEGER UNIQUE NOT NULL, + PERSONID INTEGER NOT NULL, FIRSTNAME VARCHAR(32) NOT NULL, LASTNAME VARCHAR(32) NOT NULL, MIDDLENAME VARCHAR(32), @@ -68,20 +69,26 @@ STATE CHAR(2), ZIPCODE CHAR(5), COUNTRY VARCHAR(64), - CONSTRAINT PERS_PK PRIMARY KEY (DATASTORE_IDENTITY) + CONSTRAINT PERS_PK PRIMARY KEY (DATASTORE_IDENTITY), + CONSTRAINT PERS_UK UNIQUE (PERSONID) ); CREATE TABLE employees ( DATASTORE_IDENTITY INTEGER NOT NULL, HIREDATE VARCHAR(32), WEEKLYHOURS DOUBLE, - DEPARTMENT INTEGER REFERENCES departments, - FUNDINGDEPT INTEGER REFERENCES departments, - MANAGER INTEGER REFERENCES persons, - MENTOR INTEGER REFERENCES persons, - HRADVISOR INTEGER REFERENCES persons, + DEPARTMENT INTEGER, + FUNDINGDEPT INTEGER, + MANAGER INTEGER, + MENTOR INTEGER, + HRADVISOR INTEGER, CONSTRAINT EMPS_PK PRIMARY KEY (DATASTORE_IDENTITY), - CONSTRAINT EMPS_FK FOREIGN KEY (DATASTORE_IDENTITY) REFERENCES persons (DATASTORE_IDENTITY) + CONSTRAINT EMPS_FK FOREIGN KEY (DATASTORE_IDENTITY) REFERENCES persons (DATASTORE_IDENTITY), + CONSTRAINT EMPS_DEPARTMENT FOREIGN KEY (DEPARTMENT) REFERENCES departments (DATASTORE_IDENTITY), + CONSTRAINT EMPS_FUNDINGDEPT FOREIGN KEY (FUNDINGDEPT) REFERENCES departments (DATASTORE_IDENTITY), + CONSTRAINT EMPS_MANAGER FOREIGN KEY (MANAGER) REFERENCES persons (DATASTORE_IDENTITY), + CONSTRAINT EMPS_MENTOR FOREIGN KEY (MENTOR) REFERENCES persons (DATASTORE_IDENTITY), + CONSTRAINT EMPS_HRADVISOR FOREIGN KEY (HRADVISOR) REFERENCES persons (DATASTORE_IDENTITY) ); CREATE TABLE parttimeemployees ( @@ -102,8 +109,9 @@ DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, INSID INTEGER, CARRIER VARCHAR(64) NOT NULL, - EMPLOYEE INTEGER REFERENCES persons, - CONSTRAINT INS_PK PRIMARY KEY (DATASTORE_IDENTITY) + EMPLOYEE INTEGER, + CONSTRAINT INS_PK PRIMARY KEY (DATASTORE_IDENTITY), + CONSTRAINT INS_EMPLOYEE FOREIGN KEY (EMPLOYEE) REFERENCES persons (DATASTORE_IDENTITY) ); CREATE TABLE medicalinsurance ( @@ -122,10 +130,11 @@ CREATE TABLE projects ( DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, - PROJID INTEGER UNIQUE NOT NULL, + PROJID INTEGER NOT NULL, NAME VARCHAR(32) NOT NULL, BUDGET DECIMAL(11,2) NOT NULL, - CONSTRAINT PROJS_PK PRIMARY KEY (DATASTORE_IDENTITY) + CONSTRAINT PROJS_PK PRIMARY KEY (DATASTORE_IDENTITY), + CONSTRAINT PROJS_UK UNIQUE (PROJID) ); CREATE TABLE project_reviewer ( @@ -139,9 +148,10 @@ ); CREATE TABLE employee_phoneno_type ( - EMPID INTEGER REFERENCES persons NOT NULL, + EMPID INTEGER NOT NULL, PHONENO VARCHAR(16) NOT NULL, - TYPE VARCHAR(16) NOT NULL + TYPE VARCHAR(16) NOT NULL, + CONSTRAINT employee_phoneno_type_EMPID FOREIGN KEY (EMPID) REFERENCES persons (DATASTORE_IDENTITY) ); ALTER TABLE project_reviewer Index: src/sql/derby/datastoreidentity/schema4.sql =================================================================== --- src/sql/derby/datastoreidentity/schema4.sql (revision 372795) +++ src/sql/derby/datastoreidentity/schema4.sql (working copy) @@ -47,13 +47,14 @@ ID INTEGER, NAME VARCHAR(32) NOT NULL, EMP_OF_THE_MONTH INTEGER, - COMPANYID INTEGER REFERENCES companies, - CONSTRAINT DEPTS_PK PRIMARY KEY (DATASTORE_IDENTITY) + COMPANYID INTEGER, + CONSTRAINT DEPTS_PK PRIMARY KEY (DATASTORE_IDENTITY), + CONSTRAINT DEPTS_COMPANYID FOREIGN KEY (COMPANYID) REFERENCES companies (DATASTORE_IDENTITY) ); CREATE TABLE persons ( DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, - PERSONID INTEGER UNIQUE NOT NULL, + PERSONID INTEGER NOT NULL, FIRSTNAME VARCHAR(32) NOT NULL, LASTNAME VARCHAR(32) NOT NULL, MIDDLENAME VARCHAR(32), @@ -64,42 +65,50 @@ STATE CHAR(2), ZIPCODE CHAR(5), COUNTRY VARCHAR(64), - CONSTRAINT PERS_PK PRIMARY KEY (DATASTORE_IDENTITY) + CONSTRAINT PERS_PK PRIMARY KEY (DATASTORE_IDENTITY), + CONSTRAINT PERS_UK UNIQUE (PERSONID) ); CREATE TABLE employees ( DATASTORE_IDENTITY INTEGER NOT NULL, HIREDATE VARCHAR(32), WEEKLYHOURS DOUBLE, - DEPARTMENT INTEGER REFERENCES departments, - FUNDINGDEPT INTEGER REFERENCES departments, - MANAGER INTEGER REFERENCES persons, - MENTOR INTEGER REFERENCES persons, - HRADVISOR INTEGER REFERENCES persons, + DEPARTMENT INTEGER, + FUNDINGDEPT INTEGER, + MANAGER INTEGER, + MENTOR INTEGER, + HRADVISOR INTEGER, WAGE DOUBLE, SALARY DOUBLE, DISCRIMINATOR varchar(64) NOT NULL, CONSTRAINT EMPS_PK PRIMARY KEY (DATASTORE_IDENTITY), - CONSTRAINT EMPS_FK FOREIGN KEY (DATASTORE_IDENTITY) REFERENCES persons + CONSTRAINT EMPS_FK FOREIGN KEY (DATASTORE_IDENTITY) REFERENCES persons (DATASTORE_IDENTITY), + CONSTRAINT EMPS_DEPARTMENT FOREIGN KEY (DEPARTMENT) REFERENCES departments (DATASTORE_IDENTITY), + CONSTRAINT EMPS_FUNDINGDEPT FOREIGN KEY (FUNDINGDEPT) REFERENCES departments (DATASTORE_IDENTITY), + CONSTRAINT EMPS_MANAGER FOREIGN KEY (MANAGER) REFERENCES persons (DATASTORE_IDENTITY), + CONSTRAINT EMPS_MENTOR FOREIGN KEY (MENTOR) REFERENCES persons (DATASTORE_IDENTITY), + CONSTRAINT EMPS_HRADVISOR FOREIGN KEY (HRADVISOR) REFERENCES persons (DATASTORE_IDENTITY) ); CREATE TABLE insuranceplans ( DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, INSID INTEGER, CARRIER VARCHAR(64) NOT NULL, - EMPLOYEE INTEGER REFERENCES persons, + EMPLOYEE INTEGER, PLANTYPE VARCHAR(8), LIFETIME_ORTHO_BENEFIT DECIMAL(22,3), DISCRIMINATOR varchar(64) NOT NULL, - CONSTRAINT INS_PK PRIMARY KEY (DATASTORE_IDENTITY) + CONSTRAINT INS_PK PRIMARY KEY (DATASTORE_IDENTITY), + CONSTRAINT INS_EMPLOYEE FOREIGN KEY (EMPLOYEE) REFERENCES persons (DATASTORE_IDENTITY) ); CREATE TABLE projects ( DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, - PROJID INTEGER UNIQUE NOT NULL, + PROJID INTEGER NOT NULL, NAME VARCHAR(32) NOT NULL, BUDGET DECIMAL(11,2) NOT NULL, - CONSTRAINT PROJS_PK PRIMARY KEY (DATASTORE_IDENTITY) + CONSTRAINT PROJS_PK PRIMARY KEY (DATASTORE_IDENTITY), + CONSTRAINT PROJS_UK UNIQUE (PROJID) ); CREATE TABLE project_reviewer ( @@ -113,9 +122,10 @@ ); CREATE TABLE employee_phoneno_type ( - EMPID INTEGER REFERENCES persons NOT NULL, + EMPID INTEGER NOT NULL, PHONENO VARCHAR(16) NOT NULL, - TYPE VARCHAR(16) NOT NULL + TYPE VARCHAR(16) NOT NULL, + CONSTRAINT employee_phoneno_type_EMPID FOREIGN KEY (EMPID) REFERENCES persons (DATASTORE_IDENTITY) ); ALTER TABLE project_reviewer --------------050706000505030801030703--