db-jdo-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andy Jefferson <a...@jpox.org>
Subject TCK Schema : M-N relations
Date Sat, 03 Sep 2005 19:32:45 GMT
Schema for M-N unit tests has an odd foreign-key defined currently (certainly 
for datastore identity) that causes issues. We have the following tables in 
one of the M-N's

CREATE TABLE persons (
    DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    PERSONID INTEGER UNIQUE NOT NULL,
    ...
    CONSTRAINT EMPS_PK PRIMARY KEY (DATASTORE_IDENTITY)
)
CREATE TABLE projects (
    DATASTORE_IDENTITY INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
    PROJID INTEGER UNIQUE NOT NULL,
    ...
    CONSTRAINT PROJS_PK PRIMARY KEY (DATASTORE_IDENTITY)
);
with join table
CREATE TABLE project_reviewer (
    PROJID INTEGER NOT NULL,
    REVIEWER INTEGER NOT NULL
);

However there's an FK defined as
ALTER TABLE project_reviewer
    ADD CONSTRAINT PR_PROJ_FK FOREIGN KEY
        (PROJID) REFERENCES projects(PROJID);

so the FK goes from join table project id column *not* to the datastore 
identity column in the "project" table but instead to a different column. That 
won't work - the "DATASTORE_IDENTITY" values are set by the implementation, 
whereas the PROJID is set by the TCK so likely won't concur! The definition 
of <join> in the spec is that it goes between the *primary key columns* of 
the primary table and the join table column(s). It should be mapped to the PK 
of the "projects" table, so should go to "DATASTORE_IDENTITY" not "PROJID". 
The same applies to the FK "PR_REV_FK" in the schema. 

The join table "project_member" has no FK's defined on it in the schema 
currently - so maybe that should have 2 FK's added.



The schema for the TCK also doesn't currently impose PK's on 
any of its join tables. While the TCK tests will flush out any implementation 
behaviour that is non-compliant, this could be aided by addition of PK's. For 
example on the tables "project_reviewer", "project_member".

-- 
Andy

Mime
View raw message