oracle sql plus making an existing primary key auto_increment

quinnd6 · 04-10-2007 05:04PM #1

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

The table has nothing in it.

Beano · 04-10-2007 05:06PM

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 · 04-10-2007 05:58PM

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 · 05-10-2007 10:37AM

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 · 05-10-2007 12:52PM

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 · 05-10-2007 01:00PM

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 · 05-10-2007 02:20PM

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 · 05-10-2007 02:34PM

bonkey wrote:

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

Doh! Insert is of course what I meant.

bonkey · 05-10-2007 03:11PM

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.

oracle sql plus making an existing primary key auto_increment

Comments