Hi,
I've created a row type and table hierarchy according to the script below.
But when I try to create the last FK of the script I get the error
that there is not PK on the referencing table (error -297). And indeed I did
not define a
PK on the table DOCUMENT. The primary key is created on ENTITY and is
inherited
to DOCUMENT. So I would say I could create a FK to DOCUMENT to make sure
that every
record in the table PHYS_DOC points to a DOCUMENT and not a PERSON.
I've contacted Informix support but the answer I got was not satisfying
enough for me.
They said I should make the FK to from PHYS_DOC to ENTITY. But then I'm not
sure that
every record in PHYS_DOC points to a DOCUMENT.
They also said that in version 9.11 of IDS it was possible to create FK's
that point to an inherited PK
but that they had to remove it because it was supposed to be illegal.
I'm probably missing something here. Can anybody give me a good reason why
this is
not possible or why it is 'supposed to be illegal'?
Here's the script:
----------------------------------------------------------------------------
-----------------------------------------------------------
create row type entity_t (entityid integer, name varchar(50));
create row type document_t (title varchar(50), numpages smallint) under
entity_t;
create row type person_t (firstname varchar(50), lastname varchar(50) )
under entity_t;
create table entity of type entity_t;
create table document of type document_t;
create table person of type person_t;
create table phys_doc (entityid integer, docno smallint, storagedate date,phys_doc blob);
alter table phys_doc add constraint primary key (entityid, docno) constraintpdoc_pk;
alter table entity add constraint primary key (entityid) constraint ent_pk;
alter table phys_doc add constraint foreign key (entityid) referencesdocument (entityid) constraint pdoc_doc_fk;
----------------------------------------------------------------------------
-----------------------------------------------------------
We are using Informix Dynamic Server Version 9.14.UC6.
The script has been created with Informix SQL Editor 2.00.TG1.
Thanks!
Leon