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).
Right.
smime.p7s