Identity Columns in the Oracle Database

This week's throwback feature of the week is the Identity Column. Identity Columns give you an easy way to create unique numeric identifiers on a column in your table. They are an alternative to manually doing this with sequences and triggers.

This feature was released back in Oracle Database 12c (Release 1).

This blog walks through a basic example of how to use the Identity Column in the Oracle Database.

What Are Identity Columns And Why Use Them?

Identity Columns are automated numeric fields that Oracle manages internally to create a unique ID on your table.

Simply put, Identity Columns:

- Simplify ID creation: They removing the need for manual sequence and trigger (and their management).

- Help to ensure data integrity: Automatically handle unique identifier creation reliably.

- Improve readability: Can keep table definitions clean and easier to read.

The syntax looks like this:

GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ] AS IDENTITY

How about A Real-World Example?

Imagine youv'e got a retail app and you want to keep track of each product with a unique ID. Instead of manually creating and managing sequences or triggers to do it, using Identity Columns means Oracle handles it for you automatically.

Identity Columns in Action

Let’s walk through a simple example:

Step 1: Create Table Using Identity Columns

CREATE TABLE inventory (
	inventory_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
	inventory_name VARCHAR2(100),
	price NUMBER(10, 2)
);

Here, the `GENERATED ALWAYS` means Oracle will create the ID for you.

Step 2: Insert Data

Identity columns automatically assign IDs:

INSERT INTO inventory (inventory_name, price) VALUES ('Vintage F1 TShirt', 29.99);

INSERT INTO inventory (inventory_name, price) VALUES ('Charcoal London Overcoat', 79.99);

COMMIT;

Because we're using the GENERATED ALWAYS, if we try and manually add a value to one of the products we'll see an error:

INSERT INTO inventory (inventory_id, inventory_name, price) VALUES (10, 'Baggy Jeans', 49.99);

Step 3: Flexibility with BY DEFAULT

Instead, using `GENERATED BY DEFAULT` allows us to add our own values if needed:

CREATE TABLE inventory_default (
	inventory_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- here only inserting NULL explicitly causes an error.
	inventory_name VARCHAR2(100),
	price NUMBER(10, 2)
	);

-- Automatically assigned by Oracle
INSERT INTO inventory_default (inventory_name, price) VALUES ('Jet Black Blazer', 299.99);

-- Explicitly provided ID
INSERT INTO inventory_default (inventory_id, inventory_name, price) VALUES (50, 'Golf pants', 39.99);

-- This causes an error because Identity Columns are implicitly NOT NULL
INSERT INTO inventory_default (inventory_id, inventory_name, price) VALUES (NULL, 'Rugby Jersey', 59.99);

COMMIT;

Step 4: Convenience with BY DEFAULT ON NULL

With `BY DEFAULT ON NULL`, Oracle automatically assigns an ID if NULL or no value is given:

CREATE TABLE inventory_optional (
	inventory_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY, -- Oracle generates values automatically if NULL or no value is explicitly provided.
	inventory_name VARCHAR2(100),
	price NUMBER(10, 2)
	);
	
INSERT INTO inventory_optional (inventory_name, price) VALUES ('Vintage Graphic Hoodie', 59.99);

INSERT INTO inventory_optional (inventory_id, inventory_name, price) VALUES (NULL, 'Wool Scarf', 29.99);

INSERT INTO inventory_optional (inventory_id, inventory_name, price) VALUES (100, 'Denim Jacket', 69.99);

COMMIT;

Check the inserted data:

SELECT * FROM inventory_optional;

Quick Recap

Identity Columns are a nice feature that makes managing unique identifiers easy. It's worth noting that while this feature is convenient, it may not be worth rewriting existing apps that already use sequences or triggers. Moving forward, though, it's definitely an option worth considering for new developments.

Considerations and Restrictions for the Feature

- Only one identity column allowed per table

- Must be numeric; cannot be user-defined types

- Always implicitly NOT NULL

- Not inherited when using a CREATE TABLE AS SELECT statement.

Additional Info

• Connor covers this on a youtube video nicely

• Oracle Database Docs

Cleanup

You can clean up with the following

DROP table inventory;
DROP table inventory_default;
DROP table inventory_optional;

-Killian