autoincrement of non primary key

Jeff Cox Wed, 03 Mar 2004 13:25:03 -0800

Title: Message

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:

=====

====

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

smime.p7s
Description: S/MIME cryptographic signature

autoincrement of non primary key

Reply via email to