Oracle Database – Tables, Constraints and Sequences

This is the DDL post: creating tables, the five constraint types, and how Oracle generates primary keys — which changed significantly in 12c and is still the subject of a lot of out-of-date advice.

A table worth copying

CREATE TABLE customers (
  id            NUMBER(19)      GENERATED ALWAYS AS IDENTITY,
  email         VARCHAR2(320 CHAR)  NOT NULL,
  first_name    VARCHAR2(100 CHAR),
  last_name     VARCHAR2(100 CHAR)  NOT NULL,
  status        VARCHAR2(20 CHAR)   DEFAULT 'PENDING' NOT NULL,
  credit_limit  NUMBER(12,2)        DEFAULT 0 NOT NULL,
  created_at    TIMESTAMP WITH TIME ZONE DEFAULT systimestamp NOT NULL,
  --
  CONSTRAINT customers_pk        PRIMARY KEY (id),
  CONSTRAINT customers_email_uq  UNIQUE (email),
  CONSTRAINT customers_status_ck CHECK (status IN ('PENDING', 'ACTIVE', 'CLOSED')),
  CONSTRAINT customers_credit_ck CHECK (credit_limit >= 0)
);

CREATE TABLE orders (
  id           NUMBER(19) GENERATED ALWAYS AS IDENTITY,
  customer_id  NUMBER(19) NOT NULL,
  order_date   DATE       DEFAULT sysdate NOT NULL,
  total        NUMBER(12,2) NOT NULL,
  --
  CONSTRAINT orders_pk PRIMARY KEY (id),
  CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id)
    REFERENCES customers (id) ON DELETE CASCADE
);

Three habits in there that pay off later:

• Name every constraint. Left unnamed, Oracle calls it SYS_C0011947, and that is the name in the error message your users see and in the migration script that has to drop it. Names also differ between environments, so a script that drops SYS_C0011947 works on exactly one database.
• DEFAULT before NOT NULL — that is the required order, and reversing it is a syntax error.
• Constraints at the end, not inline. Same effect, but every constraint is in one place and it reads like a specification.

The five constraints

UNIQUE and NULL

A single-column UNIQUE constraint permits any number of NULLs — two unknowns are not known to be equal. On a composite unique key Oracle rejects the row only when every column matches and none is null, so partly-null duplicates slip through:

CREATE TABLE t (a NUMBER, b NUMBER, CONSTRAINT t_uq UNIQUE (a, b));
INSERT INTO t VALUES (1, NULL);
INSERT INTO t VALUES (1, NULL);   -- accepted. Both rows now exist.

If nullable columns must still be unique together, either make them NOT NULL or index a coalesced expression.

Always index your foreign keys

This is the most commonly skipped index in Oracle, and it has two distinct consequences. Deleting a parent row makes Oracle check the child table for orphans; with no index that is a full scan of the child per delete. Worse, on an unindexed foreign key Oracle takes a share lock on the whole child table for the duration — so a delete on customers blocks unrelated inserts into orders.

CREATE INDEX orders_customer_ix ON orders (customer_id);

Find the ones you missed:

SELECT c.table_name, c.constraint_name, cc.column_name
FROM   user_constraints c
JOIN   user_cons_columns cc ON cc.constraint_name = c.constraint_name
WHERE  c.constraint_type = 'R'
AND    NOT EXISTS (
         SELECT 1 FROM user_ind_columns ic
         WHERE  ic.table_name  = cc.table_name
         AND    ic.column_name = cc.column_name
         AND    ic.column_position = cc.position)
ORDER  BY 1, 2;

ON DELETE, and the one Oracle does not have

ON DELETE CASCADE and ON DELETE SET NULL both work. There is no ON UPDATE CASCADE — Oracle's position is that a primary key you need to update is not a primary key. Use a surrogate key and you never want it.

Deferrable constraints

Occasionally you need a constraint checked at COMMIT rather than per statement — for example swapping two rows' unique values, or loading a circular reference:

ALTER TABLE t ADD CONSTRAINT t_uq UNIQUE (code)
  DEFERRABLE INITIALLY IMMEDIATE;

SET CONSTRAINT t_uq DEFERRED;   -- for this transaction only
UPDATE t SET code = 'B' WHERE id = 1;   -- transiently duplicates
UPDATE t SET code = 'A' WHERE id = 2;
COMMIT;                          -- checked here

One cost worth knowing: a deferrable unique or primary key is backed by a non-unique index, which is slightly less efficient. Don't make it the default.

Generated keys: identity columns

Since 12c, this is the answer:

id NUMBER(19) GENERATED ALWAYS AS IDENTITY          -- you cannot supply a value
id NUMBER(19) GENERATED BY DEFAULT AS IDENTITY      -- yours wins if supplied
id NUMBER(19) GENERATED BY DEFAULT ON NULL AS IDENTITY  -- generate only when NULL

GENERATED ALWAYS is the strictest and the best default — nothing can insert an id by accident. Use BY DEFAULT ON NULL when a data-migration script has to preserve existing ids.

Under the covers Oracle creates a sequence and wires it in as the column default. You can tune it in place:

id NUMBER(19) GENERATED ALWAYS AS IDENTITY (START WITH 1000 INCREMENT BY 1 CACHE 50)

-- Which sequence is behind it?
SELECT table_name, column_name, sequence_name
FROM   user_tab_identity_cols;

Generated keys: sequences

You still need explicit sequences when several tables share a number space, when the application wants the id before inserting, or when you are on 11g.

CREATE SEQUENCE order_seq START WITH 1 INCREMENT BY 1 CACHE 100 NOCYCLE;

INSERT INTO orders (id, customer_id, total)
VALUES (order_seq.NEXTVAL, 42, 99.95);

SELECT order_seq.CURRVAL FROM dual;   -- last value THIS session took

Notes that matter:

• Since 12c a sequence can be a column default (DEFAULT order_seq.NEXTVAL). The old BEFORE INSERT FOR EACH ROW trigger that every 11g schema carries is obsolete — and it was measurably slow.
• CURRVAL fails until NEXTVAL has been called in the same session (ORA-08002). It is session-local, so it is safe, just not predictable.
• CACHE n preallocates n values in memory. Fast, and it means an instance restart or a rolled-back transaction leaves a gap. Sequences guarantee uniqueness, never contiguity. If you need gapless numbering — invoice numbers — that is a different problem, solved with a counter table and accepted serialisation.
• CACHE 20 is the default and is too small for a busy insert path. 100 to 1000 is normal. This number must match Hibernate's allocationSize; see the Spring Boot post for what happens when it doesn't.

Changing tables afterwards

ALTER TABLE customers ADD phone VARCHAR2(30 CHAR);
ALTER TABLE customers ADD (phone VARCHAR2(30 CHAR), country CHAR(2));   -- several at once
ALTER TABLE customers MODIFY phone VARCHAR2(40 CHAR);                   -- widening: instant
ALTER TABLE customers MODIFY last_name NULL;                            -- drop NOT NULL
ALTER TABLE customers RENAME COLUMN phone TO mobile;
ALTER TABLE customers DROP COLUMN mobile;                                -- expensive, rewrites rows
ALTER TABLE customers SET UNUSED COLUMN mobile;                           -- instant; drop later
ALTER TABLE customers ADD CONSTRAINT customers_phone_ck CHECK (mobile IS NOT NULL) ENABLE NOVALIDATE;

Two useful escape hatches. SET UNUSED hides a column immediately and defers the row-by-row work to a later ALTER TABLE … DROP UNUSED COLUMNS, which is how you drop a column on a large table during business hours. And ENABLE NOVALIDATE applies a new constraint to future rows without validating the existing ones — the way to start enforcing a rule on a table you know has historical violations.

Adding a NOT NULL column with a default is metadata-only from 11g on: the default is recorded in the dictionary rather than written into every existing row. Instant on a billion rows. Adding a nullable column and then updating it is not.

Virtual columns

Computed on read, stored nowhere, and indexable — useful for exposing a derived value without letting it drift out of sync:

ALTER TABLE customers ADD full_name
  VARCHAR2(201 CHAR) GENERATED ALWAYS AS (first_name || ' ' || last_name) VIRTUAL;

ALTER TABLE orders ADD order_month
  VARCHAR2(7) GENERATED ALWAYS AS (to_char(order_date, 'YYYY-MM')) VIRTUAL;

CREATE INDEX orders_month_ix ON orders (order_month);

Dropping

DROP TABLE orders;                       -- goes to the recycle bin
SELECT object_name, original_name FROM user_recyclebin;
FLASHBACK TABLE orders TO BEFORE DROP;   -- undo, if you were quick

DROP TABLE orders CASCADE CONSTRAINTS PURGE;   -- child FKs too, and no recycle bin

The recycle bin has saved careers. It also silently consumes tablespace, which is why an automated teardown script should always say PURGE.

Next

Tables full of data are next to useless without the querying idioms that are specific to Oracle: DUAL, ROWNUM, NVL, MERGE and friends.