John McNally wrote:
> Colm McCartan wrote:
> (apart from the fact that torque generates
>
>>incorrect sql for foreign key references to composite primary keys -
>>another story altogether...)
> can you give an example?
Sure. Heres a table from our schema with a composite primary key:
-----------------------------------------------------------------------
<table name = "DOCUMENT_VERSION">
<column name="DOCUMENT_ID" required="true" autoIncrement="false"
primaryKey="true" type="BIGINT"/>
<column name="VERSION_ID" required="true" autoIncrement="false"
primaryKey="true" type="INTEGER"/>
<column name="COMMENTS" required="true" size="255" type="VARCHAR"/>
<foreign-key foreignTable="DOCUMENT">
<reference local="DOCUMENT_ID" foreign="DOCUMENT_ID"/>
</foreign-key>
</table>
-----------------------------------------------------------------------
referenced by the following table:
-----------------------------------------------------------------------
<table name="USER_DOCUMENT_REL">
<column name="DOCUMENT_ID" required="true" autoIncrement="false"
primaryKey="true" type="BIGINT"/>
<column name="VERSION_ID" required="true" autoIncrement="false"
primaryKey="true" type="INTEGER"/>
<column name="TURBINE_USER_ID" required="true" autoIncrement="false"
primaryKey="true" type="INTEGER"/>
<column name="REL_TYPE_ID" required="true" autoIncrement="false"
primaryKey="true" type="INTEGER"/>
<foreign-key foreignTable="DOCUMENT_VERSION">
<reference local="DOCUMENT_ID" foreign="DOCUMENT_ID"/>
</foreign-key>
<foreign-key foreignTable="DOCUMENT_VERSION">
<reference local="VERSION_ID" foreign="VERSION_ID"/>
</foreign-key>
</table>
-----------------------------------------------------------------------
Torque generates the following SQL DDL for the second table:
-----------------------------------------------------------------------
CREATE TABLE USER_DOCUMENT_REL
(
DOCUMENT_ID int8 NOT NULL,
VERSION_ID integer NOT NULL,
TURBINE_USER_ID integer NOT NULL,
REL_TYPE_ID integer NOT NULL,
PRIMARY KEY(DOCUMENT_ID,VERSION_ID,TURBINE_USER_ID,REL_TYPE_ID)
);
ALTER TABLE USER_DOCUMENT_REL
ADD CONSTRAINT DOCUMENT_ID FOREIGN KEY (DOCUMENT_ID)
REFERENCES DOCUMENT_VERSION (DOCUMENT_ID);
ALTER TABLE USER_DOCUMENT_REL
ADD CONSTRAINT VERSION_ID FOREIGN KEY (VERSION_ID)
REFERENCES DOCUMENT_VERSION (VERSION_ID);
-----------------------------------------------------------------------
This generates errors at build time with the db complaining about
invalid foreign key references (sadly, I don't have the detail of the
exceptions any more). After I hand-edited to the following, it was fine:
-----------------------------------------------------------------------
CREATE TABLE USER_DOCUMENT_REL
(
DOCUMENT_ID int8 NOT NULL,
VERSION_ID integer NOT NULL,
TURBINE_USER_ID integer NOT NULL,
REL_TYPE_ID integer NOT NULL,
PRIMARY KEY(DOCUMENT_ID,VERSION_ID,TURBINE_USER_ID,REL_TYPE_ID),
FOREIGN KEY(DOCUMENT_ID, VERSION_ID) REFERENCES DOCUMENT_VERSION
);
-----------------------------------------------------------------------
The version is: postgreSQL 7.1.2 and tdk2.1
We can easily resolve this by going to an 'artificial' autogenerated
primary key (or manually generating the db).
Let me know if you need any more info or if I have misunderstood how to
do this.
colm
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
