Home » SQL & PL/SQL » SQL & PL/SQL » create table query with auto increment for one column
create table query with auto increment for one column [message #414922] Fri, 24 July 2009 01:06 Go to next message
mr.rajeshyadav
Messages: 48
Registered: November 2007
Member
Hi,
I want to create a table in which 1 column say sequence number sud be auto incremented whenever i insert a record in the table.

Thanks in advance.
Rajesh.
Re: create table query with auto increment for one column [message #414924 is a reply to message #414922] Fri, 24 July 2009 01:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Oracle does not know an auto-increment datatype. The way to do this is to create a sequence plus a Before Insert row-level trigger.
Re: create table query with auto increment for one column [message #414925 is a reply to message #414922] Fri, 24 July 2009 01:12 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Use a trigger or use an API. This is a frequently asked question. Have you done any of your own research?
Re: create table query with auto increment for one column [message #415224 is a reply to message #414922] Sun, 26 July 2009 09:41 Go to previous messageGo to next message
puniiii
Messages: 1
Registered: July 2009
Junior Member
SQL> CREATE TABLE tab1 (
2 id NUMBER PRIMARY KEY,
3 val VARCHAR2(30)
4 );
Table created.

SQL>
SQL> CREATE SEQUENCE tab1_id_seq;
Sequence created.

SQL>
SQL> INSERT INTO tab1(id, val) VALUES (tab1_id_seq.nextval, 'row1');
1 row created.

SQL>
SQL> SELECT * FROM tab1;
ID VAL
---------- ------------------------------
1 row1




Try this it will work but it still depends on version of oracle which you are using
Re: create table query with auto increment for one column [message #415227 is a reply to message #415224] Sun, 26 July 2009 09:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the answer. Nevertheless note this is not AUTO-incremental. As already said, you need a trigger to implement this feature.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: create table query with auto increment for one column [message #415252 is a reply to message #415224] Sun, 26 July 2009 16:15 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
puniiii wrote on Sun, 26 July 2009 16:41
Try this it will work but it still depends on version of oracle which you are using

What does that mean? CREATE TABLE and INSERT INTO exist probably from the very first Oracle version; sequences were introduced in Oracle 6 (released in 1988) (I hope you don't want to imply that someone still uses this version. Though, hm, someone still *might* use it. Poor.).

So, what "version" do you have on mind?
Re: create table query with auto increment for one column [message #415280 is a reply to message #415252] Mon, 27 July 2009 01:05 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If I recall correctly, in earlier versions a sequence.nextval could only be gotten in a SELECT.
But my memory might be fooling me..
Previous Topic: Can You Explain This SQL Query - Inline View Included
Next Topic: Help on query (merged 2) 10.2.0.1
Goto Forum:
  


Current Time: Fri Dec 02 14:17:21 CST 2016

Total time taken to generate the page: 0.07344 seconds