CODEWITHSUNDEEP
oracle-databaseidentityauto-increment
Alana Storm
Alana Storm

Reputation: 166086

Oracle and auto_increment/identity

In modern versions of Oracle, is there some "standard" (stored procedure, additional CREATE syntax, etc.) way to setting up a table with auto_increment/identity style column, or are we still stuck manually creating the table, creating the sequence, and creating the trigger.

Update: I realize Oracle has no concept of an auto_increment. What I'm interested in is if any of the standard Oracle tools have automated away the creation of the sequence and trigger, or if the DBA is left to create the needed queries/commands to create the sequence and trigger themselves.

Upvotes: 3

Views: 4721

Answers (4)

cletus
cletus

Reputation: 625097

Auto-increment? Nope, sorry. You're stuck with sequences (which are generally better anyway albeit slightly less convenient).

You can however use GUIDs. Oracle has a SYS_GUID() function you can use instead.

Upvotes: 2

David Aldridge
David Aldridge

Reputation: 52386

You don't actually need a trigger, you can just reference the sequence's next value when you populate the new row into the table. You can even share a single sequence between multiple tables if you like.

Upvotes: 3

Eddie Awad
Eddie Awad

Reputation: 3729

Oracle SQL Developer gives you an option to automatically create the "create or replace trigger" code that populates a table's primary key from a sequence. To do that, from the navigator tree, right-click on the table name > Trigger > Create (PK from Sequence). It does not create the sequence for you though.

Upvotes: 3

Quassnoi
Quassnoi

Reputation: 425471

If you want a sequentially incrementing ordered values, then no, SEQUENCE is the only choice.

If you want just an identity, use SYS_GUID()

Upvotes: 5

Related Questions