Is there a way to create an auto-incrementing Guid Primary Key in an Oracle database?

Question

I mostly work with sql-server (when I do work with databases) and I am trying to learn pl-sql.

Is there any equivalent to sql-server's auto-generated Guid as primary keys in Oracle?

score 7 · Accepted Answer · edited May 23 '17 at 12:00

7

You can use SYS_GUID() to generate a GUID, and use it as DEFAULT value of a column:

CREATE TABLE test_table (
  uid_col RAW(32) DEFAULT SYS_GUID(),
  some_val VARCHAR2(10)
);

EDIT: See answers to this question for more details.

edited May 23 '17 at 12:00

Community

answered Feb 17 '10 at 22:17

Peter Lang

You'd also have to code a trigger to ensure that a user- or application-provided RAW(32) wasn't supplied, otherwise you could be subject to key collisions. Even that isn't completely certain, as triggers can be disabled and some applications (like SQL*Loader in direct path mode) can ignore triggers altogether. I'm not saying Peter's solution isn't the best availble; just that Oracle doesn't really permit completely system-generated keys. – Adam Musch Feb 18 '10 at 07:55
This is not enough, you need to add primary key constraint on column `uid_col` to maintain uniqueness of values. – ThinkJet Oct 02 '13 at 11:32

score -1 · Answer 2 · answered Oct 02 '13 at 09:58

-1

Make char or varchar2 column data type overwise raw to pass future troubles.

answered Oct 02 '13 at 09:58

Aleksey V.P.

I believe you should provide an explanation how this will help with future troubles. – default locale Oct 02 '13 at 10:19

2 Answers2