Re: TCK Schema : M-N relations

[Craig Russell]

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!

smime.p7s
Description: S/MIME cryptographic signature