Sorry, I missed the response to my question, but you should specify the fk as a composite, not as two distinct fk's.
> > <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> Should be: > > <foreign-key foreignTable="DOCUMENT_VERSION"> > > <reference local="DOCUMENT_ID" foreign="DOCUMENT_ID"/> > > <reference local="VERSION_ID" foreign="VERSION_ID"/> > > </foreign-key> john mcnally Daniel Rall wrote: > > Hi Colm. Was there ever a resolution to this? > > Daniel > > Colm McCartan <[EMAIL PROTECTED]> writes: > > > 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]> > > -- > To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> > For additional commands, e-mail: <mailto:[EMAIL PROTECTED]> -- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
