surrogated key

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
sam2929Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) 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.
0
Martyn SpencerSoftware Developer / Linux System Administrator / Managing DirectorCommented:
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).
0
slightwv (䄆 Netminder) 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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.