3

I want to create an ID column that automatically generates unique ID numbers by default.

Users won't be inserting rows via INSERT statements, rather, they'll create rows using an out-of-box application (using the attribute editing environment).

I've heard that auto-generated IDs can be achieved by creating an Oracle IDENTITY column (hint: to find the relevant section in the linked page, search for this text: Use this clause to specify an identity column.).


And so, I have successfully created a table with an IDENTITY column:

CREATE TABLE A_TEST_TABLE (ID NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY);

However, I see that the field is not nullabe:

SELECT ID FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'A_TEST_TABLE'

NULLABLE = N

This is a problem because my application tries to "do users a favor". If a field is not nullable, the application automatically inserts a zero as a dummy value. This overwrites the auto-generated ID (which of course, is not what I want). I'm guessing that it does this to avoid getting an error message from the database if a null value is committed.

To avoid this issue with my application, is it possible to make the field nullabe?

When I read the documentation, it says:

If you specify ON NULL, then Oracle Database uses the sequence generator to assign a value to the column when a subsequent INSERT statement attempts to assign a value that evaluates to NULL.

Does this imply that an IDENTITY field can be nullable? If so, how?


I recognize that I have no idea what I'm doing. I don't even think I would ultimately want an ID field that is nullable. I'm just trying to make sense of all this, and I'm failing.

  • 2
    I don't think making it nullable will work. Even if it can be done, your app will start sending 0s and the second time (row) it tries, that the UNIQUE constraint will complain. (I assume you do have a unique constraint!) – ypercubeᵀᴹ Jun 28 '17 at 19:24
  • "If a field is not nullable, the application automatically inserts a zero as a dummy value. This overwrites the auto-generated ID (which of course, is not what I want)." could you make the identity columns as generated always so you never will want to insert/update the value? leave it to the db to populate it ? – Raj Jun 29 '17 at 14:59
  • @Raj : Good idea. CREATE TABLE A_TEST_TABLE (ID NUMBER GENERATED ALWAYS AS IDENTITY);. Unfortunately, when I try to add a row to the table using the application, I get an error: Database Row Change: An unexpected failure occurred. Underlying DBMS error [ORA-32795: cannot insert into a generated always identity column][USER1.A_TEST_TABLE] – Wilson Jun 29 '17 at 15:08
  • @Wilson that is the whole point is you completely avoid populating the id column, i thought I made that clear in my comment. when identity column is defined as ALWAYS, you cannot update or insert into that column. This is a calculated safeguard. instead of identity column, could you have a pre-insert trigger and if the column value is 0 (helpfully inserted by application) or null, you replace that with a sequence.nextval ? – Raj Jun 29 '17 at 17:47
  • @Raj Ok, clearly I'm not understanding. I'll go back over this and re-assess. – Wilson Jun 29 '17 at 18:07
5

You could make the column NULLable, but not make it an IDENTITY column; instead, create a sequence, and an INSERT trigger that sets the ID value from the sequence. Prior to version 12.1, IDENTITY columns weren't available, and using a sequence was the workaround.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.