views:

343

answers:

4

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.

+2  A: 

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

If you want just an identity, use SYS_GUID()

Quassnoi
+2  A: 

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.

cletus
+2  A: 

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.

EddieAwad
+2  A: 

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.

David Aldridge
I'd reinforce that it is better practice to include the sequence in the insert statement as creating a trigger for this purpose can degrade performance.
Gary
Good background to have, particularly w/r/t trigger performance, but it should be noted this will send your SQL to non-ANSI land (which may or may not be a problem, depending on your situation).
Alan Storm