Hi Craig,
you are right. I will change the project_member table as we discussed
and check in the changes tomorrow.
Regards Michael
Hi Michael,
You won't hear any objections from the U.S. Everyone is on vacation
until Tuesday!
So let's go ahead. We can always make minor changes later.
On Sep 4, 2005, at 3:18 PM, 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.
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.
I don't know for sure, but I don't think so. I think the first pattern
creates an FK to the primary key column.
Anyone have a SQL reference handy?
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?
Yes. Let's make the FK definitions explicit.
Regards,
Craig
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
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
