db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dale Thoms" <dth...@pobox.com>
Subject Re: autoincrement of non primary key
Date Wed, 03 Mar 2004 22:17:50 GMT
  
Jeff,

>From the way you've described this, it sounds like 'ID' should be the
primary key. It is unique, autoincremented, and used in other tables
to refer to this one--sounds like a primary key to me.
Seems like the other 3 columns are just foreign keys, not primary.
But maybe I'm missing something...

  Dale


On Wed, 3 Mar 2004 16:25:09 -0500, "Jeff Cox" <jeff@cwindustries.net>
said:
> Hi,
>     I am running into a problem when trying to create a table with a
> field that is not a primary key but is to be unique and to be
> autoincremented. The XML is as follows:
>  
>  
> =====
> <table name="T_SESSION_ACTIVITY" javaName="SESSION_ACTIVITY"
> idMethod="none">
>     <column name="ID" type="INTEGER" required="true"
> autoIncrement="true"/>
>     <column name="SESSION_ID" type="INTEGER" primaryKey="true"
> required="true" autoIncrement="false"/>
>     <column name="SECTION_ID" type="INTEGER" primaryKey="true"
> required="true" autoIncrement="false"/>
>     <column name="ACTIVITY_ID" type="INTEGER" primaryKey="true"
> required="true" autoIncrement="false"/>
>     <column name="MAX_SEATS" type="INTEGER" required="true"
> autoIncrement="false"/>
>         <unique>
>             <unique-column name="ID"/>
>         </unique>
>     <foreign-key foreignTable="T_CAMP_SESSION" onUpdate="cascade"
> onDelete="cascade">
>         <reference local="SESSION_ID" foreign="ID"/>
>     </foreign-key>
>     <foreign-key foreignTable="T_ACTIVITY_SECTION" onUpdate="cascade"
> onDelete="cascade">
>         <reference local="SECTION_ID" foreign="ID"/>
>     </foreign-key>
>     <foreign-key foreignTable="T_ACTIVITY" onUpdate="cascade"
> onDelete="cascade">
>         <reference local="ACTIVITY_ID" foreign="ID"/>
>     </foreign-key>
> </table>
> 
>  
> 
> ====
> 
> Now I have been through a number of archive messages and I haven't found
> a solution. I can't set idMethod="naitive" because that causes the three
> fields that are part of the primaryKey to be autoincremented. This I
> don't want... those fields are foreign keys. This table is basically
> used to create a many to many relationship with three other tables. I
> have the ID field so that I can uniquely identify each relationship.
> Othe tables will then use ID as a foreign key.(maybe this is bad DB
> design, if you have a suggestion to improve I would appreciate that as
> well)
> 
> =====
> 
> The SQL that is generated from the above is as follows:
> 
> DROP TABLE T_SESSION_ACTIVITY CASCADE;
> 
> 
> CREATE TABLE T_SESSION_ACTIVITY
> (
>                                     ID,
>                                       -- REFERENCES T_CAMP_SESSION (ID)
>     SESSION_ID integer NOT NULL,
>                                       -- REFERENCES T_ACTIVITY_SECTION
> (ID)
>     SECTION_ID integer NOT NULL,
>                                       -- REFERENCES T_ACTIVITY (ID)
>     ACTIVITY_ID integer NOT NULL,
>                                     MAX_SEATS integer NOT NULL,
>     PRIMARY KEY (SESSION_ID,SECTION_ID,ACTIVITY_ID),
>     CONSTRAINT T_SESSION_ACTIVITY_U_1 UNIQUE (ID)
> );
> 
> =====
> 
> This unfortunatly is not even valid SQL, as the field type is missing
> for ID. What I would expect to see is as follows:
> 
> =====
> 
> DROP TABLE T_SESSION_ACTIVITY CASCADE;
> DROP SEQUENCE T_SESSION_ACTIVITY_SEQ;
> 
> CREATE SEQUENCE T_SESSION_ACITIVTY_SEQ;
> 
> CREATE TABLE T_SESSION_ACTIVITY
> (
>                                     ID integer DEFAULT
> nextval('T_SESSION_ACTIVITY_SEQ') NOT NULL,
>                                       -- REFERENCES T_CAMP_SESSION (ID)
>     SESSION_ID integer NOT NULL,
>                                       -- REFERENCES T_ACTIVITY_SECTION
> (ID)
>     SECTION_ID integer NOT NULL,
>                                       -- REFERENCES T_ACTIVITY (ID)
>     ACTIVITY_ID integer NOT NULL,
>                                     MAX_SEATS integer NOT NULL,
>     PRIMARY KEY (SESSION_ID,SECTION_ID,ACTIVITY_ID),
>     CONSTRAINT T_SESSION_ACTIVITY_U_1 UNIQUE (ID)
> );
> 
>  
> 
> Anyone have any idea what I am doing wrong?
> 
>  
> 
> BTW I am using Torque 3.1 and postgres 7.3
> 
>  
> 
>  
> 
> Thanks,
> 
> Jeff
> 


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


Mime
View raw message