I want to create an
ID column that automatically generates unique ID numbers by default.
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
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.