标签云

微信群

扫码加入我们

WeChat QR Code

It appears that there is no concept of AUTO_INCREMENT in Oracle, up until and including version 11g.How can I create a column that behaves like auto increment in Oracle 11g?


You can create a BEFORE INSERT trigger on the table and pull values out of a sequence to create auto-increment

2019年04月18日34分51秒

Identity columns are now available in Oracle 12c docs.oracle.com/cd/E16655_01/gateways.121/e22508/…

2019年04月19日34分51秒

See IDENTITY column autoincrement functionality in Oracle 12c and Auto-increment primary key in Pre 12c releases.

2019年04月18日34分51秒

Are you using using Oracle RAC? Using CACHED at the end of the statement can improve performance. If you are doing a lot of inserts in a short period (and the ordering is not important to you), consider staggered sequence insert trigger for additional performance benefits. See: dba-oracle.com/t_rac_proper_sequence_usage.htm

2019年04月18日34分51秒

I'm a n00b, can you please tell me where dept_seq came from!

1970年01月01日00分03秒

CREATE SEQUENCE dept_seq; creates dept_seq ... like a table .. but in this case its only a number that you can increase with dept_seq.NEXTVAL... see the trigger.

2019年04月19日34分51秒

As was mentioned, the original code would fail when encountering a line with ID specified. But how about this case: The trigger would assign the id (automatically) only if there was no id specified explicitly in INSERT. This would fail, right? And what is the proper way to do this?

2019年04月19日34分51秒

Is there a way to have the auto increment field begin from a defined value?

2019年04月19日34分51秒

For oracle newbies like me, the 'id' part of 'new.id' refers to the column 'id' in the table above. 'new' is a reserved word referring to the new row created

2019年04月18日34分51秒

What does CACHE 100; in CREATE SEQUENCE name_of_sequence START WITH 1 INCREMENT BY 1 CACHE 100; do?

2019年04月19日34分51秒

CACHE 100: keyword fetches next 100 numbers to memory. Normally a SEQUENCE is saved into database whenever its value changed, if you cache it, it will be saved and retrieved only if cached ones exhausted. Gives you a significant performance gain, but if database fails, you lose all cached values you didn't even use.

2019年04月18日34分51秒

A SYS_GUID() is a RAW(16), not 32.

2019年04月19日34分51秒

turbanoff - Good catch.Updated my answer.The SYS_GUID documentation declares a raw(32) which confused me.

2019年04月19日34分51秒

JustinCave I have used your approach in creating the sequence and trigger. What if I delete a row programmatically (java), will the ID(primary key) gets adjusted as well?

2019年04月19日34分51秒

This answer does not add anything not already in the accepted answer.

2019年04月19日34分51秒

JonHeller I personally say the IDENTITY example is much clearer in this answer.

2019年04月18日34分51秒

The WHEN (new.MAP_ID IS NULL) is not in the accepted answer. Upvoted.

2019年04月18日34分51秒

dcsohl, WHEN ( new.MAP_ID is null) is not a good code in this case and is already explained in the comment section by A.B.Cade under accepted answer.. have a read;)

2019年04月19日34分51秒

When I run this from CREATE OR REPLACE TRIGGER to END;, I get an "Enter Binds" window. If I click "Apply" and don't do anything else in that window, and then run the ALTER TRIGGER command, all is good, but wish there was a way to programmatically get rid of that pop-up and run everything together. If you try it altogether, you get PLS-00103: Encountered the symbol 'ALTER' and it doesn't like EXECUTE IMMEDIATE, either (same error, just says it Encountered the symbol 'EXECUTE' instead).

2019年04月19日34分51秒

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference.Link-only answers can become invalid if the linked page changes.

2019年04月18日34分51秒

What happens when a row is deleted? Will the SYS_GUID() its id values as well?

2019年04月19日34分51秒

Helpful information and interesting performance comparison. Using IDENTITY columns is better than using a TRIGGER for a SEQUENCE.

2019年04月19日34分51秒

How is this different than eugnio's answer? Plus: you don't need the select in modern Oracle versions. You can simply use:new.pk := TABLE_PK_SEQ.NEXTVAL

2019年04月18日34分51秒