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 [EMAIL PROTECTED] Engineering GmbH
mailto:[EMAIL PROTECTED] 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:[EMAIL PROTECTED]
P.S. A good JDO? O, Gasp!
--
-------------------------------------------------------------------
Michael Watzek [EMAIL PROTECTED] Engineering GmbH
mailto:[EMAIL PROTECTED] Buelowstr. 66
Tel.: ++49/30/235 520 36 10783 Berlin - Germany
Fax.: ++49/30/217 520 12 http://www.spree.de/
-------------------------------------------------------------------
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
