db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jeff Cox" <j...@cwindustries.net>
Subject autoincrement of non primary key
Date Wed, 03 Mar 2004 21:25:09 GMT
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


Mime
View raw message