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 Tech@Spree Engineering GmbH
>>> mailto:mbo.tech@spree.de 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:Craig.Russell@sun.com
>>
>> P.S. A good JDO? O, Gasp!
>>
>>
>
>
|