You are viewing a plain text version of this content. The canonical link for it is here.
Posted to jdo-dev@db.apache.org by Michael Watzek <mw...@spree.de> on 2005/07/26 11:14:31 UTC
ALTER TABLE clauses in TCK schemas
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
--
-------------------------------------------------------------------
Michael Watzek Tech@Spree Engineering GmbH
mailto:mwa.tech@spree.de Buelowstr. 66
Tel.: ++49/30/235 520 36 10783 Berlin - Germany
Fax.: ++49/30/217 520 12 http://www.spree.de/
-------------------------------------------------------------------
Re: ALTER TABLE clauses in TCK schemas
Posted by Michelle Caisse <Mi...@Sun.COM>.
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
>>
>>
>>
>>
>
>
Re: ALTER TABLE clauses in TCK schemas
Posted by Michael Watzek <mw...@spree.de>.
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
>
>
>
--
-------------------------------------------------------------------
Michael Watzek Tech@Spree Engineering GmbH
mailto:mwa.tech@spree.de Buelowstr. 66
Tel.: ++49/30/235 520 36 10783 Berlin - Germany
Fax.: ++49/30/217 520 12 http://www.spree.de/
-------------------------------------------------------------------
Re: ALTER TABLE clauses in TCK schemas
Posted by Michael Watzek <mw...@spree.de>.
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
--
-------------------------------------------------------------------
Michael Watzek Tech@Spree Engineering GmbH
mailto:mwa.tech@spree.de Buelowstr. 66
Tel.: ++49/30/235 520 36 10783 Berlin - Germany
Fax.: ++49/30/217 520 12 http://www.spree.de/
-------------------------------------------------------------------
Re: ALTER TABLE clauses in TCK schemas
Posted by Michael Watzek <mw...@spree.de>.
Hi Michelle,
that's fine. I noticed that datastoreidentity0.project_reviewer still
contains a reference clause on column "REVIEWER". Is that on purpose?
Regards,
Michael
> Thanks for noticing this, Michael. I will delete the REFERENCES part of
> the CREATE TABLE statements. I added the ALTER TABLE statements in
> order to have a named constraint, so I want to keep those.
>
> -- Michelle
>
> Michael Watzek wrote:
>
>> 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
>
>
--
-------------------------------------------------------------------
Michael Watzek Tech@Spree Engineering GmbH
mailto:mwa.tech@spree.de Buelowstr. 66
Tel.: ++49/30/235 520 36 10783 Berlin - Germany
Fax.: ++49/30/217 520 12 http://www.spree.de/
-------------------------------------------------------------------
Re: ALTER TABLE clauses in TCK schemas
Posted by Michelle Caisse <Mi...@Sun.COM>.
Thanks for noticing this, Michael. I will delete the REFERENCES part of
the CREATE TABLE statements. I added the ALTER TABLE statements in
order to have a named constraint, so I want to keep those.
-- Michelle
Michael Watzek wrote:
> 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