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:[EMAIL PROTECTED] Sent: 04 March 2004 15:55 To: [EMAIL PROTECTED] 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:[EMAIL PROTECTED] 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
