If you have ever worked with MySQL databases, you might be familiar with the auto_increment column. In Oracle, we use an identity column to allow the database engine to automatically generate a numeric value for each row added to the table at a specific interval.
Oracle Identity Column
The Oracle identity column is available in version 12c and above. The identity column automatically generates a numerical value for every row inserted into the table.
Since the database engine generates a unique value for each row added to the table, it works as a surrogate primary key in the database.
In Oracle, we define an identity column using the syntax below:
AS IDENTITY [ ( identity_options ) ]
The statement parameters, as seen above, include the following:
- GENERATED – We start with the GENERATED keyword, where the values are auto-generated by the database engine.
- ALWAYS – similarly, this value ensures that only the database engine can add values to the column. Attempting to add values manually will result in an error.
- BY DEFAULT – this will cause an error if you provide a NULL value to the column.
- BY DEFAULT ON NULL – oracle will generate a value instead of returning an error on NULL input.
- AS IDENTITY – this defines that this is an identity column.
- START WITH value – defines the initial value used for the identity column. By default, the value will start at 1.
- INCREMENT BY value – defines the interval value between the generated values. By default, the function will use an interval of 1.
- CACHE – defines the number of values that the database engine will generate beforehand to increase performance. This option is useful when you need to autogenerate many records.
Oracle Identity Columns
Let us discover basic examples of how to use the identity column in Oracle databases.
Oracle Create Identity Column
The most common and recommended way of creating an identity column to a table is during the creation.
An example below demonstrates how we can accomplish this:
product_id NUMBER GENERATED ALWAYS AS IDENTITY,
product_name VARCHAR2(255) NOT NULL,
quantity NUMBER NOT NULL CHECK (quantity > 0),
price NUMBER,
PRIMARY KEY (product_id)
);
The statement above creates a table called products with an identity column of product_id.
We can then insert some sample records into the table as:
In this case, we did not specify the value for the product_id column in the table above. This will allow the database engine to generate values for the added columns automatically. As mentioned, Oracle will start at the value of 1 and increment by 1 for each row added.
As mentioned, attempting to add a value for the identity column will result in an error:
Resulting error:
This is because the identity column is set to generate always.
Example 2 – Oracle Identity Generated By Default.
We can also change the identity column from GENERATED ALWAYS to GENERATED BY DEFAULT as:
product_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
product_name VARCHAR2(255) NOT NULL,
quantity NUMBER NOT NULL CHECK (quantity > 0),
price NUMBER,
PRIMARY KEY (product_id)
);
In this case, we can manually add a value for the product_id column.
Similarly, if we do not provide a value for the column, the database engine will generate it automatically.
You cannot insert a NULL value into an identity column as defined above.
Example 3
We can also define the start with and the interval values during table creation as shown:
product_id NUMBER GENERATED BY DEFAULT AS IDENTITY START WITH 10 INCREMENT BY 2,
product_name VARCHAR2(255) NOT NULL,
quantity NUMBER NOT NULL CHECK (quantity > 0),
price NUMBER,
PRIMARY KEY (product_id)
);
In this case, the autogenerated values will start at ten and increment by 2 for each new row added to the table.
Points to Note
Although an identity column does provide hefty convenience when working with a large dataset, it does come with restrictions. These include:
- A table can only hold one identity column.
- An identity column cannot contain a default constraint.
- A create table cannot inherit an identity column as select statement.
- The value of an identity column can only be a numeric type.
Conclusion
In this post, you discovered the various methods and techniques of working with the identity column in Oracle databases.
