You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-user@db.apache.org by Malte Stien <ma...@stien.de> on 2003/06/05 06:47:48 UTC
Multiple primary keys?
Hi
Does each table in Torque have to have exactly one primary key? I have a table
(SimpleQuestion) where the primary identifiers (IssueID and ProjectElementID)
are in fact a pair of foreign keys that reference another table. The
referenced table (BasicQuestion) contains two columns whose values are not
unique on their own but in combination they are.
I already tried to specify both columns (IssueID and ProjectElementID) as
primaryKey=true. But unfortunately, that does not work. Torque generates an
SQL-schema which is not valid for Postgresql.
In that case I would like to specify no primary key but specify that pair of
columns in the referencing and the referenced table (SimpleQuestion and
BasicQuestion) as unique and each of the two columns as not null
(required=true) which according to the Postgresql user's guide should lead to
the same result at least on the database level.
But Torque creates code that cannot be compiled by javac:
BaseSimpleQuestion.java:211: cannot resolve symbol
symbol : method retrieveByPK (java.lang.Integer,java.lang.Integer)
location: class au.com.softimp.sodis.model.BasicQuestionPeer
aBasicQuestion = BasicQuestionPeer.retrieveByPK(this.issueid,
this.projectelementid);
If anybody would have any ideas that would be highly appreaciated.
Regards,
Malte.
The schema of the involved two tables follows here:
<!-- ********************** BasicQuestion ********************** -->
<table name="BasicQuestion" description="" idMethod="none">
<column
name="IssueID"
required="true"
type="INTEGER"
description=""/>
<column
name="ProjectElementID"
required="true"
type="INTEGER"
description=""/>
<foreign-key foreignTable="Issue">
<!-- ModelRelationShip R1 -->
<reference
local="IssueID"
foreign="IssueID"/>
</foreign-key>
<foreign-key foreignTable="ProjectElement">
<!-- ModelRelationShip R1 -->
<reference
local="ProjectElementID"
foreign="ProjectElementID"/>
</foreign-key>
<unique>
<unique-column name="ProjectElementID"/>
<unique-column name="IssueID"/>
</unique>
</table>
<!-- ********************** SimpleQuestion ********************** -->
<table name="SimpleQuestion" description="" idMethod="none">
<column
name="AnalysisDataItemID"
required="true"
type="INTEGER"
description=""/>
<column
name="IssueID"
required="true"
type="INTEGER"
description=""/>
<column
name="ProjectElementID"
required="true"
type="INTEGER"
description=""/>
<foreign-key foreignTable="BasicQuestion">
<!-- ModelRelationShip R2 -->
<reference
local="IssueID"
foreign="IssueID"/>
<reference
local="ProjectElementID"
foreign="ProjectElementID"/>
</foreign-key>
<foreign-key foreignTable="Question">
<!-- ModelRelationShip R4 -->
<reference
local="AnalysisDataItemID"
foreign="AnalysisDataItemID"/>
</foreign-key>
</table>
Re: Multiple primary keys?
Posted by John Dietz <jo...@stonetab.com>.
What you are requesting is perfectly normal for a mapping table to
represent a many to many relationship. I've done the exact same thing
in many places. Here's an example of what I have that works perfectly,
this one actually has 3 fields combining for the primary key. This
builds and compiles fine for me, though I am using MySQL and not
Postgresql. I don't think Torque will work unless you define at least
one primary key.
<table name="SG_MEMBER" idMethod="none" description="Member Table">
<column name="user_id"
required="true"
primaryKey="true"
type="INTEGER"
description="User ID"/>
<column name="group_id"
required="true"
primaryKey="true"
type="INTEGER"
description="Group ID"/>
<column name="member_type"
required="true"
primaryKey="true"
type="INTEGER"
description="member Type ID"/>
<foreign-key foreignTable="SG_USER">
<reference local="user_id"
foreign="user_id"/>
</foreign-key>
<foreign-key foreignTable="SG_GROUP">
<reference local="group_id"
foreign="group_id"/>
</foreign-key>
</table>
John Dietz
On Wednesday, June 4, 2003, at 09:47 PM, Malte Stien wrote:
> Hi
>
> Does each table in Torque have to have exactly one primary key? I have
> a table
> (SimpleQuestion) where the primary identifiers (IssueID and
> ProjectElementID)
> are in fact a pair of foreign keys that reference another table. The
> referenced table (BasicQuestion) contains two columns whose values are
> not
> unique on their own but in combination they are.
>
> I already tried to specify both columns (IssueID and ProjectElementID)
> as
> primaryKey=true. But unfortunately, that does not work. Torque
> generates an
> SQL-schema which is not valid for Postgresql.
>
> In that case I would like to specify no primary key but specify that
> pair of
> columns in the referencing and the referenced table (SimpleQuestion and
> BasicQuestion) as unique and each of the two columns as not null
> (required=true) which according to the Postgresql user's guide should
> lead to
> the same result at least on the database level.
>
> But Torque creates code that cannot be compiled by javac:
> BaseSimpleQuestion.java:211: cannot resolve symbol
> symbol : method retrieveByPK (java.lang.Integer,java.lang.Integer)
> location: class au.com.softimp.sodis.model.BasicQuestionPeer
> aBasicQuestion = BasicQuestionPeer.retrieveByPK(this.issueid,
> this.projectelementid);
>
> If anybody would have any ideas that would be highly appreaciated.
> Regards,
> Malte.
>
> The schema of the involved two tables follows here:
> <!-- ********************** BasicQuestion ********************** -->
> <table name="BasicQuestion" description="" idMethod="none">
> <column
> name="IssueID"
> required="true"
> type="INTEGER"
> description=""/>
> <column
> name="ProjectElementID"
> required="true"
> type="INTEGER"
> description=""/>
> <foreign-key foreignTable="Issue">
> <!-- ModelRelationShip R1 -->
> <reference
> local="IssueID"
> foreign="IssueID"/>
> </foreign-key>
> <foreign-key foreignTable="ProjectElement">
> <!-- ModelRelationShip R1 -->
> <reference
> local="ProjectElementID"
> foreign="ProjectElementID"/>
> </foreign-key>
> <unique>
> <unique-column name="ProjectElementID"/>
> <unique-column name="IssueID"/>
> </unique>
> </table>
> <!-- ********************** SimpleQuestion ********************** -->
> <table name="SimpleQuestion" description="" idMethod="none">
> <column
> name="AnalysisDataItemID"
> required="true"
> type="INTEGER"
> description=""/>
> <column
> name="IssueID"
> required="true"
> type="INTEGER"
> description=""/>
> <column
> name="ProjectElementID"
> required="true"
> type="INTEGER"
> description=""/>
> <foreign-key foreignTable="BasicQuestion">
> <!-- ModelRelationShip R2 -->
> <reference
> local="IssueID"
> foreign="IssueID"/>
> <reference
> local="ProjectElementID"
> foreign="ProjectElementID"/>
> </foreign-key>
> <foreign-key foreignTable="Question">
> <!-- ModelRelationShip R4 -->
> <reference
> local="AnalysisDataItemID"
> foreign="AnalysisDataItemID"/>
> </foreign-key>
> </table>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
RE: Multiple primary keys?
Posted by Swarn Dhaliwal <sd...@aem-east.com>.
Hi,
One of the solutions to your problem could be to create an id field in
your table which will serve as sort of an implementation primary key as
opposed to the business primary key (which is the combination of two
columns you mentioned). That way Torque would not complain, and you can
use your primary key combination the way you are attempting to use now.
Hope this helps,
Swarn
-----Original Message-----
From: Malte Stien [mailto:malte@stien.de]
Sent: Thursday, June 05, 2003 12:48 AM
To: Turbine Torque Users List
Subject: Multiple primary keys?
Hi
Does each table in Torque have to have exactly one primary key? I have a
table
(SimpleQuestion) where the primary identifiers (IssueID and
ProjectElementID)
are in fact a pair of foreign keys that reference another table. The
referenced table (BasicQuestion) contains two columns whose values are
not
unique on their own but in combination they are.
I already tried to specify both columns (IssueID and ProjectElementID)
as
primaryKey=true. But unfortunately, that does not work. Torque generates
an
SQL-schema which is not valid for Postgresql.
In that case I would like to specify no primary key but specify that
pair
of
columns in the referencing and the referenced table (SimpleQuestion and
BasicQuestion) as unique and each of the two columns as not null
(required=true) which according to the Postgresql user's guide should
lead
to
the same result at least on the database level.
But Torque creates code that cannot be compiled by javac:
BaseSimpleQuestion.java:211: cannot resolve symbol
symbol : method retrieveByPK (java.lang.Integer,java.lang.Integer)
location: class au.com.softimp.sodis.model.BasicQuestionPeer
aBasicQuestion = BasicQuestionPeer.retrieveByPK(this.issueid,
this.projectelementid);
If anybody would have any ideas that would be highly appreaciated.
Regards,
Malte.
The schema of the involved two tables follows here:
<!-- ********************** BasicQuestion ********************** -->
<table name="BasicQuestion" description="" idMethod="none">
<column
name="IssueID"
required="true"
type="INTEGER"
description=""/>
<column
name="ProjectElementID"
required="true"
type="INTEGER"
description=""/>
<foreign-key foreignTable="Issue">
<!-- ModelRelationShip R1 -->
<reference
local="IssueID"
foreign="IssueID"/>
</foreign-key>
<foreign-key foreignTable="ProjectElement">
<!-- ModelRelationShip R1 -->
<reference
local="ProjectElementID"
foreign="ProjectElementID"/>
</foreign-key>
<unique>
<unique-column name="ProjectElementID"/>
<unique-column name="IssueID"/>
</unique>
</table>
<!-- ********************** SimpleQuestion ********************** -->
<table name="SimpleQuestion" description="" idMethod="none">
<column
name="AnalysisDataItemID"
required="true"
type="INTEGER"
description=""/>
<column
name="IssueID"
required="true"
type="INTEGER"
description=""/>
<column
name="ProjectElementID"
required="true"
type="INTEGER"
description=""/>
<foreign-key foreignTable="BasicQuestion">
<!-- ModelRelationShip R2 -->
<reference
local="IssueID"
foreign="IssueID"/>
<reference
local="ProjectElementID"
foreign="ProjectElementID"/>
</foreign-key>
<foreign-key foreignTable="Question">
<!-- ModelRelationShip R4 -->
<reference
local="AnalysisDataItemID"
foreign="AnalysisDataItemID"/>
</foreign-key>
</table>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org