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 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!
>>>
>>>
>>>
>>
>>
>> --
>> 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!
>
>
--
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
|