CREATE TABLE and constraints: rules the data can't break

A table is columns with types. Constraints are the rules on top: this column is the key, that one must exist in another table, these two together are unique, this one must be between 1 and 99. Put the rules in the table and every program, report and late-night fix has to obey them.

The test case

Squad registrations for 2027: an auto-numbered id, a team (which must exist), a player name (required), a shirt number 1-99 (unique within the team) and a role (BAT, BOWL, AR or WK). Then try seven registrations and see which the table accepts.

The teams to point at, and a log for the attempts:

Setup

begin
  for t in (select table_name from user_tables
            where  table_name in ('D100_A', 'D100_B', 'D100_C', 'D100_TEAMS', 'D100_LOG')
            order  by case when table_name = 'D100_TEAMS' then 2 else 1 end) loop
    execute immediate 'drop table ' || t.table_name || ' purge';
  end loop;
end;
/

Setup

create table d100_teams as select team_id, team_code, team_name from cr_teams;

Setup

alter table d100_teams add constraint d100_teams_pk primary key (team_id);

Setup

create table d100_log (tbl varchar2(10), row_no number, result varchar2(20));

Way 1: constraints written on the columns

Setup

create table d100_a (
  reg_id      number generated always as identity constraint d100a_pk primary key,
  team_id     number       not null constraint d100a_team_fk references d100_teams,
  player_name varchar2(40) not null,
  shirt_no    number(2)    constraint d100a_shirt_ck check (shirt_no between 1 and 99),
  role        varchar2(4)  constraint d100a_role_ck check (role in ('BAT', 'BOWL', 'AR', 'WK')),
  constraint d100a_shirt_uk unique (team_id, shirt_no)
);

A constraint on a column (inline) covers just that column. The UNIQUE on two columns has to be written separately, after the columns.

Way 2: constraints listed after the columns

Setup

create table d100_b (
  reg_id      number generated always as identity,
  team_id     number       not null,
  player_name varchar2(40) not null,
  shirt_no    number(2),
  role        varchar2(4),
  constraint d100b_pk       primary key (reg_id),
  constraint d100b_team_fk  foreign key (team_id) references d100_teams (team_id),
  constraint d100b_shirt_ck check (shirt_no between 1 and 99),
  constraint d100b_role_ck  check (role in ('BAT', 'BOWL', 'AR', 'WK')),
  constraint d100b_shirt_uk unique (team_id, shirt_no)
);

Same rules, all in one place at the bottom. Many teams prefer this: the column list stays short, and the rules read like a checklist.

Way 3: create the table, then ALTER TABLE ADD CONSTRAINT

Setup

create table d100_c (
  reg_id      number generated always as identity,
  team_id     number       not null,
  player_name varchar2(40) not null,
  shirt_no    number(2),
  role        varchar2(4)
);

Setup

alter table d100_c add (
  constraint d100c_pk       primary key (reg_id),
  constraint d100c_team_fk  foreign key (team_id) references d100_teams (team_id),
  constraint d100c_shirt_ck check (shirt_no between 1 and 99),
  constraint d100c_role_ck  check (role in ('BAT', 'BOWL', 'AR', 'WK')),
  constraint d100c_shirt_uk unique (team_id, shirt_no)
);

How schema scripts are often organised: all the tables first, then all the keys, so the order of the CREATE TABLEs doesn't matter.

Seven registrations

The same seven inserts against each table, logging what happened (0 means accepted):

Setup

declare
  type t_row is record (team_id number, player_name varchar2(40), shirt_no number, role varchar2(4));
  type t_rows is table of t_row;
  l_rows t_rows := t_rows(
    t_row(2, 'Arun Pillai',  7,  'BAT'),
    t_row(2, 'Dev Menon',    7,  'BOWL'),
    t_row(9, 'Ghost Player', 10, 'AR'),
    t_row(3, 'Ollie Grant',  0,  'WK'),
    t_row(3, null,           11, 'BAT'),
    t_row(3, 'Ben Ashby',    12, 'KEEP'),
    t_row(3, 'Max Doyle',    12, 'WK'));
  l_code number;
begin
  for t in (select column_value as tbl from table(sys.odcivarchar2list('D100_A', 'D100_B', 'D100_C'))) loop
    for i in 1 .. l_rows.count loop
      begin
        execute immediate 'insert into ' || t.tbl || ' (team_id, player_name, shirt_no, role) values (:1, :2, :3, :4)'
          using l_rows(i).team_id, l_rows(i).player_name, l_rows(i).shirt_no, l_rows(i).role;
        insert into d100_log values (t.tbl, i, 'accepted');
      exception when others then
        l_code := sqlcode;     -- SQLCODE can't be used inside a SQL statement, so copy it first
        insert into d100_log values (t.tbl, i, 'ORA-' || to_char(-l_code, 'fm00000'));
      end;
    end loop;
  end loop;
  commit;
end;
/

Expected result: what each table accepted

select row_no,
       max(case when tbl = 'D100_A' then result end) as way_1,
       max(case when tbl = 'D100_B' then result end) as way_2,
       max(case when tbl = 'D100_C' then result end) as way_3
from   d100_log
group  by row_no
order  by row_no;

Row by row: Dev Menon wanted Arun's number 7 (unique), team 9 doesn't exist (foreign key), shirt 0 is outside 1-99 (check), no name (not null), KEEP isn't a role (check), and Max Doyle is fine. Two signings out of seven. The registrar is going to need a coffee.

Expected result: the rules, as the database sees them

select cons, constraint_type, columns, count(*) as tables
from   (select substr(c.constraint_name, 7) as cons, c.constraint_type,
               (select listagg(cc.column_name, ',') within group (order by cc.position)
                from   user_cons_columns cc
                where  cc.constraint_name = c.constraint_name) as columns
        from   user_constraints c
        where  c.table_name in ('D100_A', 'D100_B', 'D100_C')
        and    c.generated = 'USER NAME')
group  by cons, constraint_type, columns
order  by cons;

All three ways produce the same five rules on all three tables. USER_CONSTRAINTS is where the database keeps them: type P primary key, R foreign key ("referential"), U unique, C check (and NOT NULL, which we filtered out because those got system names like SYS_C0012345).

19c and 26ai

The same on both. On 26ai, CREATE TABLE IF NOT EXISTS and DROP TABLE IF EXISTS replace the drop-if-there block at the top (page 12.7); 26ai can also use a DOMAIN for "shirt number 1-99" and reuse it across tables.

Gotchas

• Name your constraints. ORA-00001: unique constraint (KS.D100A_SHIRT_UK) violated tells you what went wrong; (KS.SYS_C0098765) doesn't.
• CHECK passes on null. shirt_no between 1 and 99 is unknown for a null shirt, and unknown isn't false, so the row goes in. Add NOT NULL if the value is required.
• Foreign keys need an index on the child column in practice (here team_id), or deleting a parent row locks the whole child table while Oracle checks it.
• Identity numbers have gaps. Each failed insert above still used up an id, so Max Doyle isn't reg_id 2. Never rely on identity values being consecutive.