oracle中产生auto_increment列的解决办法（转）

scudetto

Tet Cheng -- Thanks for the question regarding "Difference using
Auto-increment col and using Attribute key", version Version 7.2/7.3 or
8.1.5
originally submitted on 5-Aug-2000 1:33 Eastern US time, last updated 5-Oct-2005 7:41
Tom's latest followup
|
GOTO a Bookmarkable Page
|
Bottom
You Asked (
Jump to Tom's latest followup
)
Hi Tom,
    I am doing some Data Modelling and my target database is Oracle.  Unlike
other Databases like SQLServer and Access, Oracle does not define a
Auto-Increment Column.  I may need to use an Auto-Incrementing column as a
Primary key for a table but I can also choose another attribute of datatype
Varchar2(30) which is unique also in the table.  Can you tell me why Oracle does
not implement Auto-increment Columns as a DataType and the advantages and
disadvantages of using both the auto-increment column and the attribute as
primary keys?
Thanks
Tet Cheng

[color="red"]and we said...
We offer a sequence which has a little more flexibility and control.  To get an
autoincrement column, I would:
create table T ( x int primary key, .... );
create sequence t_seq;
create trigger t_trigger before insert on T for each row
begin
   if ( :new.x is null ) then
      select t_seq.nextval into :new.x from dual;
   end if;
end;
/
to automatically populate when a value for X was not supplied (allowing me to
easily override the "auto" part of it) or I would:
create table T ( x int primary key, .... );
create sequence t_seq;
and then:
insert into t ( x, ... ) values ( t_seq.nextval, .... );
The nice thing about sequences is when you are populating a parent/child table
-- you have immediate access to t_seq.CURRVAL which returns the value of the
last NEXTVAL you selected.
As for "..and the advantages and disadvantages of using both the auto-increment
column and the attribute as primary keys?"  I don't fully understand that as an
"auto increment" column is in fact an attribute as well....


  [color="white"]Reviews
   

GOTO a page to Bookmark Review
|
Bottom
|
Top

  November  12, 2001
Reviewer: 
A reader
  
GOTO a page to Bookmark Review
|
Bottom
|
Top

Can't incrementation of sequence be controlled?  January   09, 2002
Reviewer: 
Godwin
 from Ghana
Hi Tom,
With this example given
i.e create table T(x int primary key...);
create sequence t_seq;
and then;
insert into t(x,...) values (t_seq_nextval, ...);
Now i did this but after i inserted like 3 rows into table t
and later deleted all the 3 rows in table t, I again insert another row but this
time the sequence continued from 4 instead of starting again from 1 since all
the previous 3 rows were deleted.
How can i ensure that the sequence starts from 1 again when the rows have been
deleted.
Followup:
You cannot, you do not want it to, it would be a totally inefficient thing to
do.
Sequences do not, will not, cannot give you a "gap free" set of numbers.
Sequences do one thing - they give you a unique number in a highly scalable
fashion.

GOTO a page to Bookmark Review
|
Bottom
|
Top

  January   10, 2002
Reviewer: 
Saif
Hi GodWin
You can also do it as following
create trigger t_trigger
before insert on table_name
for each row
begin
select max(nvl(col_seq,0))+1
into :new.x
from table_name ;
end ;
/
If any number value is already present in it, it will autometically generate
next number in sequence. If there is no value, as in your case, it will generate
first sequence number and with this method, there is no chance to loose any
sequence number
I think, this will also help you.
AM I right Tom?
Followup:
try that in a multi user environment some day and see what happens. (hint -- if
you and I insert at the same time, we'll generate the SAME primary key value).
Do this only if you want to build the least scalable, slowest possible system.
If scaling and performance are not any concern to you -- this is perhaps
acceptable.
Also, insert three rows, delete where x = 2; and see the resulting "gap".
The quest for the "holy grail" of a gap free sequence of numbers in a relational
database that is suppose to perform and scale has always (and forever will)
confuse the heck out of me.
GOTO a page to Bookmark Review
|
Bottom
|
Top

Autosequencing  October   04, 2005
Reviewer: 
Tyrone
 from Tauranga New Zealand
Fantastic help, Im currently doing my degree and this site has been one heck of
a help in my Oracle papers, every question I could think of had already been
answered, BETTER THAN GOOGLEING IT !!!!!!!
Followup:
ok, you just said I'm better than "google"
that is the nicest thing anyone ever said to me :)
(google rocks though, it is in general 'much better')
GOTO a page to Bookmark Review
|
Bottom
|
Top

Impact of nice looking data  October   05, 2005
Reviewer: 
Marc Blum
 from Aachen, Germany
by the way:
in my experience, developers tend to design data models to deliver "nice
looking" data. Examples are
- gapless IDs
- ascending IDs
- new columns to be placed in the middle of the table definition
- columns with redundant/derived data
"Requierements" like these render the application unscalabe or at least consume
coding resources with no benefit to the application at all.
GOTO a page to Bookmark Review
|
Bottom
|
Top

Tom,  October   05, 2005
Reviewer: 
A reader
did you ever *try*
www.google.nz
?
Followup:
www.google.nz could not be found. Please check the name and try again.
GOTO a page to Bookmark Review
|
Bottom
|
Top

Ahem.  October   05, 2005
Reviewer: 
Mick in UK
 from London  UK
I think you mean  www.google.co.nz
which I have quickly tested against www.google.co.uk
and the results look identical.
But Toms site is still an excellent resource.
This is the first time I have posted here but
Thanks tom for all your help over the years.
I always search your site first then google.
With sequences I tend to encourage our developers to use a stored procedure for
inserts     and have the values clause  use   seq_idcol.nextval      ...   and
most times the insert has a returning clause. So that the developer can get
their id back from the       procedure call.
Cheers
p.s.  Tom, I think the way you handle Mikito is always very
professional.  And while some times wacky, his questions always present an
opportunity to test another view point.
He is advancing the science of wacky words at least.
thanks again to you both


本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u/4330/showart_54669.html