DDL: Tables and Constraints

5.1 Creating Tables

CREATE TABLE customers (
    customer_id  NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
    first_name   VARCHAR2(50 CHAR)  NOT NULL,
    last_name    VARCHAR2(50 CHAR)  NOT NULL,
    email        VARCHAR2(255)      CONSTRAINT uq_customers_email UNIQUE,
    city         VARCHAR2(60 CHAR),
    country      VARCHAR2(60 CHAR)  DEFAULT 'India' NOT NULL,
    created_at   TIMESTAMP          DEFAULT SYSTIMESTAMP NOT NULL,
    CONSTRAINT pk_customers PRIMARY KEY (customer_id)
);

CREATE TABLE categories (
    category_id        NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    category_name      VARCHAR2(100) NOT NULL UNIQUE,
    parent_category_id NUMBER REFERENCES categories(category_id)
);

CREATE TABLE products (
    product_id   NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_name VARCHAR2(150) NOT NULL,
    category_id  NUMBER NOT NULL,
    unit_price   NUMBER(10,2) NOT NULL CONSTRAINT ck_products_price CHECK (unit_price >= 0),
    stock        NUMBER DEFAULT 0 NOT NULL CHECK (stock >= 0),
    is_active    BOOLEAN DEFAULT TRUE,                         -- 23ai (else NUMBER(1) / CHAR(1))
    CONSTRAINT fk_products_categories FOREIGN KEY (categor
…

5.2 Constraints

Creates or uses a unique index. Allows multiple NULLs.

UNIQUE + NOT NULL. One per table.

ON DELETE CASCADE / ON DELETE SET NULL. Oracle has no ON UPDATE CASCADE.

Cannot reference other rows, SYSDATE, sequences, or other tables Constraint states: ENABLE / DISABLE

VALIDATE / NOVALIDATE (whether existing data is checked)

DEFERRABLE INITIALLY IMMEDIATE | DEFERRED (checked at commit)

RELY / NORELY (used for query rewrite) Always index foreign key columns. Unindexed FKs cause table-level lock (TM) contention on parent updates and deletes.

ALTER TABLE orders ADD CONSTRAINT ck_ship CHECK (ship_city IS NOT NULL) ENABLE NOVALIDATE;
ALTER TABLE orders DISABLE CONSTRAINT ck_ship;
ALTER TABLE orders MODIFY CONSTRAINT ck_ship ENABLE VALIDATE;
SET CONSTRAINTS ALL DEFERRED;
SELECT constraint_name, constraint_type, status, validated FROM user_constraints WHERE table_name = 'ORDERS';

5.3 Identity Columns vs Sequences

GENERATED ALWAYS AS IDENTITY: you can't supply a value.

GENERATED BY DEFAULT [ON NULL] AS IDENTITY: you can override it.

Identity columns are backed by an internal sequence (ISEQ$$_nnn).

Before 12c the pattern was a sequence plus a BEFORE INSERT trigger. Since 12c, DEFAULT seq.NEXTVAL also works.

5.4 Altering Tables

ALTER TABLE customers ADD (phone VARCHAR2(20));
ALTER TABLE customers MODIFY (phone VARCHAR2(30) NOT NULL);
ALTER TABLE customers RENAME COLUMN city TO city_name;
ALTER TABLE customers DROP COLUMN phone;
ALTER TABLE customers SET UNUSED (phone);   ALTER TABLE customers DROP UNUSED COLUMNS;
ALTER TABLE customers READ ONLY;  ALTER TABLE customers READ WRITE;
ALTER TABLE customers MODIFY (email INVISIBLE);   -- invisible column (12c)
RENAME customers TO clients;
COMMENT ON TABLE customers IS 'Registered shop customers';
COMMENT ON COLUMN customers.email IS 'Primary contact email';
ALTER TABLE customers ANNOTATIONS (Owner 'Sales', Sensitivity 'PII');   -- 23ai

5.5 DROP, TRUNCATE, DELETE and the Recycle Bin

DDL (auto-commit) Yes (every row) Flashback from the recycle bin

TRUNCATE TABLE staging_sales DROP STORAGE;
TRUNCATE TABLE parent_tbl CASCADE;            -- 12c, needs ON DELETE CASCADE FKs
DROP TABLE staging_sales;                     -- goes to the recycle bin
DROP TABLE staging_sales PURGE;               -- permanent
DROP TABLE IF EXISTS staging_sales;           -- 23ai
SHOW RECYCLEBIN
FLASHBACK TABLE staging_sales TO BEFORE DROP;
PURGE RECYCLEBIN;

5.6 Special Table Types

Heap-organized (default) Rows stored in no particular order Index-Organized Table (IOT)

Rows stored in a B-tree by PK: ORGANIZATION INDEX Global Temporary Table (GTT)

Definition is permanent, data is private per session or transaction Private Temporary Table (18c)

Definition and data live in memory, prefix ORA$PTT_ External table Reads OS files (Module 34) Clustered table

Index or hash cluster: co-locates rows of multiple tables Partitioned table Blockchain / Immutable tables Tamper-proof (21c/19.11+) Object tables Rows are object instances Sharded / duplicated tables

CREATE GLOBAL TEMPORARY TABLE gtt_cart (product_id NUMBER, qty NUMBER)
ON COMMIT DELETE ROWS;          -- or PRESERVE ROWS (session-level)

CREATE PRIVATE TEMPORARY TABLE ora$ptt_tmp (id NUMBER) ON COMMIT DROP DEFINITION;

CREATE TABLE country_iot (code CHAR(2) PRIMARY KEY, name VARCHAR2(60)) ORGANIZATION INDEX;

CREATE TABLE emp_copy AS SELECT * FROM employees WHERE 1 = 0;   -- CTAS (structure only)