About setting ID for the record [message #6709] |
Fri, 02 May 2003 07:08 |
Ken
Messages: 50 Registered: April 1998
|
Member |
|
|
Suppose I have a table called member which is used to store the information of members. The primary key of this table is the ID of the member, which I will set it when user insert the valid record. The user is not allowed to enter the ID.
After checking all fields are valid, I will use
(count(*) from MEMBER +1) this method to set the ID, is it a good solution??
|
|
|
Re: About setting ID for the record [message #6712 is a reply to message #6709] |
Fri, 02 May 2003 07:34 |
Naveen
Messages: 75 Registered: June 2001
|
Member |
|
|
Hi,
I would say your approach is wrong. It would be always better to use sequences to insert the values. For a single user your above query works fine but when multiple users are trying to insert the record at the same time it would give proble. Create a sequence and use yourseq.nextval to insert.
---Naveen.
|
|
|
Re: About setting ID for the record [message #6713 is a reply to message #6709] |
Fri, 02 May 2003 07:41 |
Art Metzer
Messages: 2480 Registered: December 2002
|
Senior Member |
|
|
quote from kEn:
----------------------------------------------------------------------
Suppose I have a table called member which is used to store the information of members. The primary key of this table is the ID of the member, which I will set it when user insert the valid record. The user is not allowed to enter the ID.
After checking all fields are valid, I will use
(count(*) from MEMBER +1) this method to set the ID, is it a good solution??
----------------------------------------------------------------------
No, this is not a good solution. Here's why, and the conventional alternative.
First of all, say you have 20 members, ids 1 through 20. You delete member #8. What will be the id of the next member you add?
Also, there's nothing to prevent two simultaneous users, each trying to add a distinct member, from hitting your COUNT(*) query at the same time and each thereby attempting to add a row with the same id.
The customary solution to this problem in Oracle is to use a sequence.
HTH,
A
|
|
|
Re: About setting ID for the record [message #6714 is a reply to message #6713] |
Fri, 02 May 2003 09:36 |
Ken
Messages: 50 Registered: April 1998
|
Member |
|
|
Thanks guy, there's another question, can I use sequence in Oracle Form?
Since I create a button in the form to check whether all fields are valid, then it will go to the next record..I set the primary key of the table MEMBER invisible and then enter this statement in the button trigger:
:MEMBER.ID := MEM_SEQUENCE.NEXTVAL;
but it is unable to compile ...
|
|
|
|
|
|
|