Identity columns, sequences and virtual columns

Three ways a column can fill itself in:

• Identity (GENERATED ... AS IDENTITY, since 12c): the table numbers its own rows.
• Default from a sequence (DEFAULT my_seq.NEXTVAL, since 12c): a separate number generator, shareable across tables.
• Virtual column (GENERATED ALWAYS AS (expression)): computed from other columns whenever it's read, never stored.

The test case

A "batting card" table: an id starting at 1000, the batter, runs and balls, and a strike rate that calculates itself. Load the top three 2026 run-scorers, most runs first.

Setup

begin
  for t in (select table_name from user_tables where table_name in ('D101_A', 'D101_B', 'D101_C')) loop
    execute immediate 'drop table ' || t.table_name || ' purge';
  end loop;
  for s in (select sequence_name from user_sequences where sequence_name in ('D101_B_SEQ', 'D101_C_SEQ')) loop
    execute immediate 'drop sequence ' || s.sequence_name;
  end loop;
end;
/

Expected result

Ids 1000, 1001, 1002 from all three tables, and strike rates nobody typed in.

Way 1: identity column

Setup

create table d101_a (
  card_id     number generated always as identity (start with 1000) primary key,
  player_name varchar2(40) not null,
  runs        number not null,
  balls       number not null,
  strike_rate number generated always as (round(100 * runs / nullif(balls, 0), 1)) virtual
);

• GENERATED ALWAYS means you can't supply your own value (ORA-32795 if you try). GENERATED BY DEFAULT lets you, and BY DEFAULT ON NULL fills it in when you pass null.
• Behind the scenes Oracle creates a sequence named like ISEQ$$_12345 and owns it.

Way 2: DEFAULT sequence.NEXTVAL

Setup

create sequence d101_b_seq start with 1000;

Setup

create table d101_b (
  card_id     number default d101_b_seq.nextval primary key,
  player_name varchar2(40) not null,
  runs        number not null,
  balls       number not null,
  strike_rate number generated always as (round(100 * runs / nullif(balls, 0), 1)) virtual
);

You own the sequence: other tables can share it, and you can ALTER SEQUENCE it. Anyone can still insert their own card_id if they want to.

Way 3: a trigger (the pre-12c way)

Setup

create sequence d101_c_seq start with 1000;

Setup

create table d101_c (
  card_id     number primary key,
  player_name varchar2(40) not null,
  runs        number not null,
  balls       number not null,
  strike_rate number generated always as (round(100 * runs / nullif(balls, 0), 1)) virtual
);

Setup

create or replace trigger d101_c_bi
before insert on d101_c for each row
when (new.card_id is null)
begin
  :new.card_id := d101_c_seq.nextval;
end;
/

You'll find thousands of these in older schemas. They work, but they're slower than identity or DEFAULT and one more object to maintain.

Load the same three rows into each table

Setup

begin
  for r in (select p.player_name, sum(b.runs_batter) as runs,
                   sum(case when b.extra_type = 'WD' then 0 else 1 end) as balls
            from   cr_balls_v b
            join   cr_players p on p.player_id = b.batter_id
            where  b.season = 2026
            group  by p.player_name
            order  by runs desc
            fetch  first 3 rows only) loop
    insert into d101_a (player_name, runs, balls) values (r.player_name, r.runs, r.balls);
    insert into d101_b (player_name, runs, balls) values (r.player_name, r.runs, r.balls);
    insert into d101_c (player_name, runs, balls) values (r.player_name, r.runs, r.balls);
  end loop;
  commit;
end;
/

None of the inserts mention card_id or strike_rate: the tables fill them in.

select card_id, player_name, runs, balls, strike_rate from d101_a order by card_id;

select card_id, player_name, runs, balls, strike_rate from d101_b order by card_id;

select card_id, player_name, runs, balls, strike_rate from d101_c order by card_id;

19c and 26ai

The same on both.

Gotchas

• Virtual columns can't be inserted or updated, only read. They can be indexed, though, which makes them handy for searching on an expression.
• Sequences are not gap-free. Rollbacks, failed inserts and the sequence cache (default 20 numbers, lost on restart) all leave holes. For invoice numbers that legally can't have gaps, you need something else.
• Identity columns and data copies: to copy rows with their ids into a GENERATED ALWAYS table, you'd have to switch it to BY DEFAULT first.