Hi,
I checked in the changes (revision 278730). I included three changes wrt
Andys patch:
- I applied all the changes to schema[1-4].sql, too.
- I removed the foreign key definition from the CREATE TABLE statement
for project_member, since we have a separate ALTER TABLE defining the
foreign key.
- I changed all the REFERENCES clauses to not include an explicit column
specification such that it always refers the primary key, e.g. I changed
(EMP_OF_THE_MONTH) REFERENCES persons(DATASTORE_IDENTITY);
to
(EMP_OF_THE_MONTH) REFERENCES persons;
Most of the FK definitions used the latter form already, but not all of
them.
Regards Michael
Hi Michael,
Michael Bouschen wrote:
Hi Craig,
Hi Michael,
I believe that the patch applies to the other
datastoreidentityschemafiles as well.
Yes, this is what I think, too. I will check in the change for all
datastoreidentity schema files unless I hear any objections.
Any idea why one join table uses this pattern:
CREATE TABLE project_member (
PROJID INTEGER REFERENCES projects NOT NULL,
MEMBER INTEGER REFERENCES persons NOT NULL);
and others use this pattern?
CREATE TABLE project_reviewer (
PROJID INTEGER NOT NULL,
REVIEWER INTEGER NOT NULL
);
ALTER TABLE project_reviewer
ADD CONSTRAINT PR_PROJ_FK FOREIGN KEY
(PROJID) REFERENCES projects(PROJID);
ALTER TABLE project_reviewer
ADD CONSTRAINT PR_REV_FK FOREIGN KEY
(REVIEWER) REFERENCES persons(PERSONID);
I guess the latter allows us to name the foreign key so it's easier
to delete them by name.
I changed the table definitions and added the constraints in an ALTER
TABLE statement so that we would have named constraints for a test. I
don't remember which one now. That one form specifies the column list
and the other defaults it is whimsy.
No I have no idea.
This is a good catch. I guess the first pattern creates a FK to the
column having the same name in the referenced table.
When the column list is omitted the referenced column(s) is(are) the
primary key of the referenced table.
But we want the FK to reference a different column called
DATASTORE_IDENTITY. Andys patch adds ALTER TABLE definitions for table
project_member, so we should skip the REFERENCES clause from its
definition:
CREATE TABLE project_member (
PROJID INTEGER NOT NULL,
MEMBER INTEGER NOT NULL
);
What do you think?
Yup.
-- Michelle
Regards Michael
Craig
On Sep 4, 2005, at 2:55 PM, Michael Bouschen wrote:
Hi Andy,
thanks for the patch!
I think the changes also apply to the other datastoreidentity schema
files: schema[1-4].sql, since they have exactly the same problem. Am
I right? I have patched all 5 schema files in my workspace. I just
want to double check before I check in the changes.
Regards Michael
Hi Craig,
I'd be happy if you could propose a patch fixing the FK's.
patch is attached. Not raised a JIRA because in the time taken to
raise the JIRA somebody could just have applied the patch
------------------------------------------------------------------------
Index: test/sql/derby/datastoreidentity/schema.sql
===================================================================
--- test/sql/derby/datastoreidentity/schema.sql (revision 267234)
+++ test/sql/derby/datastoreidentity/schema.sql (working copy)
@@ -167,12 +167,20 @@
ALTER TABLE project_reviewer ADD CONSTRAINT PR_PROJ_FK FOREIGN KEY
- (PROJID) REFERENCES projects(PROJID);
+ (PROJID) REFERENCES projects(DATASTORE_IDENTITY);
ALTER TABLE project_reviewer ADD CONSTRAINT PR_REV_FK FOREIGN KEY
- (REVIEWER) REFERENCES persons(PERSONID);
+ (REVIEWER) REFERENCES persons(DATASTORE_IDENTITY);
+ALTER TABLE project_member + ADD CONSTRAINT PM_PROJ_FK FOREIGN KEY
+ (PROJID) REFERENCES projects(DATASTORE_IDENTITY);
+
+ALTER TABLE project_member + ADD CONSTRAINT PM_MEMB_FK FOREIGN KEY
+ (MEMBER) REFERENCES persons(DATASTORE_IDENTITY);
+
ALTER TABLE departments ADD CONSTRAINT EMP_MO_FK FOREIGN KEY
(EMP_OF_THE_MONTH) REFERENCES persons(DATASTORE_IDENTITY);
--
Michael Bouschen [EMAIL PROTECTED] Engineering GmbH
mailto:[EMAIL PROTECTED] http://www.tech.spree.de/
Tel.:++49/30/235 520-33 Buelowstr. 66
Fax.:++49/30/2175 2012 D-10783 Berlin
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 Bouschen [EMAIL PROTECTED] Engineering GmbH
mailto:[EMAIL PROTECTED] http://www.tech.spree.de/
Tel.:++49/30/235 520-33 Buelowstr. 66
Fax.:++49/30/2175 2012 D-10783 Berlin
