surrogated key

sam2929
sam2929 used Ask the Experts™
on
Hi,
How can I make typeid as surrogated key

create table type
typeid number(10),---this as surrogated key
typecode varchar2(10 byte)
)

result as
typeid typecode

1         aa
2       bb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
A surrogate key is one that is made up for the purpose of having a primary key.

https://en.wikipedia.org/wiki/Surrogate_key

There isn't a magic parameter that "makes" it.
Martyn SpencerSoftware Developer / Linux System Administrator / Managing Director

Commented:
Assuming you want a numeric field, the way I do it is to use a sequence and a trigger that acts before insert to assign the sequence value to the surrogate key.

For example:

create table t_test (
  test_id number (10),
  code varchar2(20)
);

create sequence seq_test start with 1 increment by 1;

create or replace trigger seq_test
  before insert
  on t_test
  referencing old as old new as new
  for each row
begin
  select seq_test.nextval into :new.test_id from dual;
end;
/

Open in new window

Note that sequences do not guarantee no gaps. My declaration generally results in a sequence that increments by 1 each time it is used, but rolled back transactions, for example, will cause gaps to appear. Also because the sequence is cached, restarting the database may cause gaps to appear as well. The "nocache" directive when creating a sequence prevents the latter but degrades performance for little benefit (imho).
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
If Martyn is correct and you are asking about how to create an auto-number column:
Starting in 12c there is now an IDENTITY column:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/CREATE-TABLE.html#GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial