Identity Column Gotcha in Oracle 19c
oraclepl/sqlI recently created a new table in a database that I maintain and decided to use the identity column feature to have unique IDs in my table. In the past when I was using an older version of Oracle, this feature was not available, so I had to create a sequence and a trigger each time I wanted to have a table with an auto-generated ID. So I created that table with the identity column to always generate an ID. Then to fill the table with data, I used an insert all statement. To my surprise, all the IDs for all the rows were the same. Turns out you need to insert one row at a time for the Identity column to work correctly.
An example to replicate:
-- Create the table
create table userlist
(id int generated always as identity,
username varchar2(10) not null);
-- Insert the values
insert all
into userlist (username) values ('Phil')
into userlist (username) values ('Bob')
into userlist (username) values ('Charlie')
select * from dual;
commit;
-- Query the table
-- You will see here that all the users have the same ID.
select * from userlist;The resulting table will look something like this:
| ID | USERNAME |
|---|---|
| 1 | Phil |
| 1 | Bob |
| 1 | Charlie |
To prevent this from happening, you just have to add PRIMARY KEY to the definition of the ID column. That way when you come across this issue you will get an error that won’t let you add those rows instead. In older versions, primary keys were defined by adding a constraint, but this way is easier.
create table userlist
(id int generated always as identity primary key,
username varchar2(10) not null);