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 Jeff Cox <je...@cwindustries.net> on 2004/03/03 22:25:09 UTC
autoincrement of non primary key
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
RE: autoincrement of non primary key
Posted by Jeff Cox <je...@cwindustries.net>.
Dale,
First let me say I really appreciate you responding to my question.
Secondly, I would agree with you and in fact considered that possibility
early on in the design, but the truth is I need any given combination of
(SESSION_ID, SECTION_ID, ACTIVITY_ID) to be unique. I don't know of
another way to enforce this in the schema, except by making them the
primary key. If there is another way to do this my problem would
certainly be solved...
Though it doesn't seem to answer why what I currently have causes Torque
to generate incorrect SQL syntax. Other than the fact that what I am
trying to do is a poor design, something very possible....
Jeff
-----Original Message-----
From: Dale Thoms [mailto:dthoms@pobox.com]
Sent: Wednesday, March 03, 2004 5:18 PM
To: Apache Torque Users List
Subject: Re: autoincrement of non primary key
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" <je...@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
Re: autoincrement of non primary key
Posted by Dale Thoms <dt...@pobox.com>.
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" <je...@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