Home » SQL & PL/SQL » SQL & PL/SQL » About setting ID for the record
About setting ID for the record [message #6709] Fri, 02 May 2003 07:08 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 ...
Re: About setting ID for the record [message #6715 is a reply to message #6714] Fri, 02 May 2003 09:50 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
See this FAQ entry.

For some odd reason you can't do a direct assign to a sequence's NEXTVAL.

Instead, you have to
SELECT mem_sequence.NEXTVAL INTO :member.id FROM DUAL;
Good luck,

A
Re: About setting ID for the record [message #6716 is a reply to message #6715] Fri, 02 May 2003 09:56 Go to previous messageGo to next message
Ken
Messages: 50
Registered: April 1998
Member
Thank a lot~~
Re: About setting ID for the record [message #6799 is a reply to message #6716] Tue, 06 May 2003 13:09 Go to previous messageGo to next message
anam
Messages: 14
Registered: December 2002
Junior Member
u can also use seq in initial value.
Re: About setting ID for the record [message #6800 is a reply to message #6709] Tue, 06 May 2003 13:12 Go to previous message
anam
Messages: 14
Registered: December 2002
Junior Member
u can use (max(*) from member +1) instead of count. with this u will not rec the prob mentioned by art metzer in first ans.
Previous Topic: UPDATE/INSERT data in Oracle Form
Next Topic: Procedure Hangs, have to kill the process?
Goto Forum:
  


Current Time: Fri Mar 29 05:34:18 CDT 2024