| Autogenerated keys [message #374394] |
Sun, 10 June 2001 02:39  |
chetana
Messages: 11
Registered: June 2001
|
Junior Member |
|
|
In SQL Server I could define a primary key that was an identity column.Which means I did not have to specifically create a primary key using max number.It was automatically created by the database at the time of insert.
Can this be done in oracle??
|
|
|
|
| Re: Autogenerated keys [message #374418 is a reply to message #374394] |
Mon, 11 June 2001 08:00  |
Bala
Messages: 205
Registered: November 1999
|
Senior Member |
|
|
Hi,
In Oracle you can create auto increment number as an object called 'sequence'.
First create a sequence,
SQL> create sequence my_seq;
this will create a sequence number start with 1 and autoincrement by 1. This is the default, can be changed by specifying.
then while inserting into table...
SQL> insert into t(my_seq.nextval, col2, col3);
you will select the nextval method from the sequence object you have created.
Every time you select the nextval, the sequence will automatically increment by 1.
Bala.
|
|
|
|
| Re: Autogenerated keys [message #374419 is a reply to message #374394] |
Mon, 11 June 2001 08:07 |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
Or, to automate the whole process, create a Before Row Insert trigger on the table and specify the value of the primary key field there:
CREATE OR REPLACE TRIGGER bri_test
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
:new.table_name.keyfield := sequence.nextval;
END;
|
|
|
|
| Re: Autogenerated keys [message #374436 is a reply to message #374394] |
Tue, 12 June 2001 01:58 |
Arvind L
Messages: 27
Registered: June 2001
|
Junior Member |
|
|
One Problem with sequnces in 8.0 and below is if your Insert statement fails because of a error with some other column in your Insert statement, the Sequence will jump.
Ex
create sequence seq start with 10 increment by 10;
Let the seq.nextval be 20
insert into emp(empno,sal) values (seq.nextval,'JOHN');
This insert statements fails to data type mismatch.
insert into emp(empno,sal) values (seq.nextval,1000);
The value inserted will be 30 instead of 20.
As a solution to this problem try this sql statement
insert into emp(empno,sal) values( (select max(empno) from emp) + 10,1000);
This will ensure a true sequence
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
