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]>
