Can an Oracle IDENTITY column be nullable

database-designidentitynulloracleunique-constraint

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.

Best Answer

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.