db-jdo-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Craig Russell <Craig.Russ...@Sun.COM>
Subject Re: TCK Schema : M-N relations
Date Sun, 04 Sep 2005 22:27:40 GMT
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!


Mime
View raw message