9

How to find out if a column is an auto increment field in oracle?

halfer
  • 19,824
  • 17
  • 99
  • 186
nakiya
  • 14,063
  • 21
  • 79
  • 118

2 Answers2

7

You can't. Oracle doesn't have auto-increment fields.

Oracle supports sequences, which are separate database objects for generating numbers. These seuqences can be used in the insert statement or in a before insert trigger to generate a primary key value for the new record.

The fields themselves are just normal field and there is no relation between the sequence and the field.

So the only hope is to parse a trigger and see if you can find evidence of it being filled there using a sequence. That would be a tough job, though, and I doubt if you can make this reliable enough. After all, the trigger could fire a function which returns the value, so you have to go all the way if you want to be sure.

GolezTrol
  • 114,394
  • 18
  • 182
  • 210
  • 1
    If there even is a trigger. Often this is done in application code (in the INSERT statement). – Thilo Feb 19 '11 at 10:01
  • select trigger_name,trigger_type,status from dba_triggers where table_name = 'nameofyourtable' order by status, trigger_name – GolezTrol Feb 19 '11 at 10:03
  • @Thilo, very well possible, but even harder to name it an 'auto incremental' field then. :) – GolezTrol Feb 19 '11 at 10:04
  • possibly all inserts are done by a stored procedure using a sequence, that yields clearly an auto incremental field – bernd_k Feb 19 '11 at 13:07
  • Actually it is perfectly simple to find whether a given trigger uses a sequence. Check out this answer: http://stackoverflow.com/questions/1730043/find-if-column-has-a-sequence/1730144#1730144 But I agree it is harder to see which column is populated with the sequence value – APC Feb 19 '11 at 21:38
4

As of Oracle 12.1 identity columns (with self generated sequences) can be identified by looking at the "DATA_DEFAULT" column of view DBA_TAB_COLUMS (or USER_TAB_COLUMNS or ALL_TAB_COLUMNS). They will look like "#OWNER"."ISEQ$$_nnnnn".nextval

user3861225
  • 41
  • 2