db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Srilakshmanan, Lakshman" <Lakshman.Srilakshma...@auspost.com.au>
Subject RE: autoincrement of non primary key
Date Thu, 04 Mar 2004 05:09:02 GMT

Hi,

I am no expert but from my limited understanding of databases I understand
the following :

The fundamental principle of a primary key is to be unique.
The fundamental principle of a non primary key is to have duplicate.
What we are attempting to do here is to have a non-primary key behave like a
primary key.

If we require a non primary key to be unique then I think you may be better
of
Implementing a trigger to do a select to detect the presence of the key
combination
SESSION_ID = 10
SECTION_ID = 15
ACTIVITY_ID = 30

If the record exists then it could "raise an error" which would be treated
as an exception in your code.
You may have a composite index on (SESSION_ID, SECTION_ID, ACTIVITY_ID) if
you have a large amount of data and you are concerned about performance.

Hope this helps

Lakshman


-----Original Message-----
From: Jeff Cox [mailto:jeff@cwindustries.net] 
Sent: 04 March 2004 15:55
To: torque-user@db.apache.org
Subject: RE: autoincrement of non primary key


Unless I am missing something, <unique> only makes the individual field
unique. What I need is the combination to be unique. In other words... 

If 
SESSION_ID = 10
SECTION_ID = 15
ACTIVITY_ID = 25

then 10, 15, and 25 can't be again used together.. but

SESSION_ID = 10
SECTION_ID = 15
ACTIVITY_ID = 30

Would work because even though SESSION_ID and SECTION_ID have been 10 and 15
before, they have not been in combination with 30.

I could just make ID the key but then the following would be allowed

ID = 1
SESSION_ID = 10
SECTION_ID = 15
ACTIVITY_ID = 25

ID = 2
SESSION_ID = 10
SECTION_ID = 15
ACTIVITY_ID = 25

Etc. etc.... 

Jeff

-----Original Message-----
From: Dave Newton [mailto:dave@solaraccess.com] 
Sent: Wednesday, March 03, 2004 9:10 PM
To: Apache Torque Users List
Subject: RE: autoincrement of non primary key

On Wed, 2004-03-03 at 20:52, Jeff Cox wrote:
> 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...

The <unique> stuff doesn't work?

Wait, you need _any_combination_ of those three values to be unique? I don't
get it. If any combination of those needs to be unique then you only need
one number.

Dave



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


Australia Post is committed to providing our customers with excellent service. If we can assist
you in any way please telephone 13 13 18 or visit our website www.auspost.com.au.

CAUTION

This e-mail and any files transmitted with it are privileged and confidential information
intended for the use of the addressee. The confidentiality and/or privilege in this e-mail
is not waived, lost or destroyed if it has been transmitted to you in error. If you have received
this e-mail in error you must (a) not disseminate, copy or take any action in reliance on
it; (b) please notify Australia Post immediately by return e-mail to the sender; and (c) please
delete the original e-mail.

---------------------------------------------------------------------
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