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: ALTER TABLE clauses in TCK schemas
Date Tue, 26 Jul 2005 15:32:18 GMT
Hi Michael,

I fixed this yesterday in commit r225174 by adding UNIQUE NOT NULL 
constraints to the referenced fields.

-- Michelle

Michael Watzek wrote:

> Hi Michelle,
>
> the "ALTER TABLE" statements below fail for datastore identity because 
> there is no unique key on the referenced columns:
>
>     [java] ij> ALTER TABLE project_reviewer
>     [java]     ADD CONSTRAINT PR_PROJ_FK FOREIGN KEY
>     [java]         (PROJID) REFERENCES projects(PROJID);
>     [java] ERROR X0Y44: Constraint 'PR_PROJ_FK' is invalid: there is 
> no unique or primary key constraint on table 'DATAS
> TOREIDENTITY3.PROJECTS' that matches the number and types of the 
> columns in the foreign key.
>     [java] ij> ALTER TABLE project_reviewer
>     [java]     ADD CONSTRAINT PR_REV_FK FOREIGN KEY
>     [java]         (REVIEWER) REFERENCES persons(PERSONID);
>     [java] ERROR X0Y44: Constraint 'PR_REV_FK' is invalid: there is no 
> unique or primary key constraint on table 'DATAST
> OREIDENTITY3.PERSONS' that matches the number and types of the columns 
> in the foreign key.
>
> I suggest to reference column "DATASTORE_IDENTITY" in both statements 
> instead.
>
> Regards,
> Michael
>
>> Hi Michelle,
>>
>> I just noticed that the foreign key names in the "ALTER TABLE" 
>> statements below are used in the orm meta data. For this reason, I 
>> suggest to keep the "ALTER TABLE" statements and instead drop the 
>> REFERENCE clauses in the column defintions below.
>>
>> Regards,
>> Michael
>>
>>> Hi Michelle,
>>>
>>> the TCK schemas for application identity and datastore indentity 
>>> both contain two "ALTER TABLE" statements on table 
>>> "project_reviewer". I think, both statements are redundant, because 
>>> in the "CREATE TABLE" statement of table "project_reviewer" column 
>>> "PROJID" as well as column "REVIEWER" have "REFERENCE" clauses already:
>>>
>>> CREATE TABLE project_reviewer (
>>>     PROJID INTEGER REFERENCES projects NOT NULL,
>>>     REVIEWER INTEGER REFERENCES persons 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);
>>>
>>> For this reason, I suggest to delete both "ALTER TABLE" statements.
>>>
>>> Regards,
>>> Michael
>>
>>
>>
>>
>
>


Mime
View raw message