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.

Improve this question
7
  • 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, 2017 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, 2017 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]
    – User1974
    Jun 29, 2017 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, 2017 at 17:47
  • @Raj Ok, clearly I'm not understanding. I'll go back over this and re-assess.
    – User1974
    Jun 29, 2017 at 18:07

1 Answer 1

Reset to default
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.

Improve this answer
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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