|
Home > Archive > Apache JDO Project > July 2005 > ALTER TABLE clauses in TCK schemas
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
ALTER TABLE clauses in TCK schemas
|
|
| Michael Watzek 2005-07-26, 7:45 am |
| 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/
-------------------------------------------------------------------
| |
| Michael Watzek 2005-07-26, 5:45 pm |
| 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/
-------------------------------------------------------------------
| |
| Michael Watzek 2005-07-26, 5:45 pm |
| 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
>
>
>
>
--
-------------------------------------------------------------------
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/
-------------------------------------------------------------------
| |
| Michelle Caisse 2005-07-26, 5:45 pm |
| 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
| |
| Michelle Caisse 2005-07-26, 5:45 pm |
| 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
>
>
>
| |
| Michael Watzek 2005-07-27, 7:45 am |
| 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:
>
>
>
--
-------------------------------------------------------------------
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/
-------------------------------------------------------------------
|
|
|
|
|