db-jdo-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michelle Caisse <Michelle.Cai...@Sun.COM>
Subject Re: TCK Schema : M-N relations
Date Mon, 05 Sep 2005 03:26:03 GMT
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!
>>
>>
>
>


Mime
View raw message