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
