How can we manage the gap in a primary key column created
by a sequence?
Ex:a company has empno as primary key generated by a
sequence and some employees leaves in between.What is the
best way to manage this gap?
- 2 Answers
- 7790 Views
- Infosys, I also Faced
- E-Mail Answers
Answer Posted / harikanthkoneti
yes...yes........ you can do it simply by updating that
table using rownum yar. see my example below.
SQL> create table test(sno number(2),sname varchar2(20));
Table created.
now if you have the data in the table like follows.
SQL> select * from test;
SNO SNAME
---------- --------------------
1 sdf
3 sdf
6 ewr
7 rt
90 drt
now just do the following simple statement.
SQL> update test set sno=rownum;
5 rows updated.
now here is the o/p for eliminating the gap generated by the
sequence.
SQL> select * from test;
SNO SNAME
---------- --------------------
1 sdf
2 sdf
3 ewr
4 rt
5 drt
then do the commit. thats all....................
now enjoy with this coding
suppose if you want to allocate the with any kind of series
of values for updating the table just follow the below
procedure
SQL> update test set sno=sql.nextval;
NOTE:sql is the sequence.
this statement will updates ur table with
what ever you given with the start with value and it ll
increment by which value you had given in the increment by
clause.
| Is This Answer Correct ? | 1 Yes | 3 No |
Post New Answer View All Answers
Using the relations and the rules set out in the notes under each relation, write table create statements for the relations EMPLOYEE, FIRE and DESPATCH. You should aim to provide each constraint with a formal name, for example table_column_pk.
How do I find my oracle client driver version?
how can db_files > maxdatafiles since db_files is for instance and the later is for database
Who developed oracle & when?
Why oracle is used?
What do you understand by a database object? Can you list a few of them?
Can we connect to ORACLE db using Windows Authentication?
What is set operator oracle?
What is the Tune Query
What is BBED in Oracle?
How to run create database statement again?
What is a data lock in oracle?
What is the difference between PFILE and SPFILE in Oracle?
How to open a cursor variable?
What are the limitations of check constraint?
