oracle sql plus making an existing primary key auto_increment

quinnd6

Can I change an existing primary key in a table to auto_increment?

The table has nothing in it.

Beano

there's no such thing as an auto-increment field in oracle. you can use a sequence to simulate an auto-increment field though.

*edit* the above information is good up to Oracle 9. After that I dont have a clue

bonkey

Beano is correct in that there is no auto_incrememnt feature directly implemented in Oracle.

I did, however, run a quick google on "oracle auto_increment" and the second hit returned gives a decent workaround.

Tom Dunne

Beano wrote:

*edit* the above information is good up to Oracle 9. After that I dont have a clue

Yip, same in 10.

As the chaps are saying. You need a trigger and a sequence.

Giblet

You just need to create a sequence, then call sequencename.nextval in your field to use it. (or currval to check what it is)

Tom Dunne

Giblet wrote:

You just need to create a sequence, then call sequencename.nextval in your field to use it. (or currval to check what it is)

Then how does it auto-increment upon an update?

bonkey

tom dunne wrote:

Then how does it auto-increment upon an update?

Why would you want it to auto-increment on an update?

Tom Dunne

bonkey wrote:

Why would you want it to auto-increment on an update?

Doh! Insert is of course what I meant.

bonkey

I think what Giblet is describing is how to use a sequence to provide the functionality....which is fine if you have control over who can run inserts and make sure that this technique is always used.

If you do it with a sequence inside a before-insert trigger, then it doesn't matter who runs inserts, nor how.