Chapter 6

Constraints, Indexes, and DDL Migration

Migrating a schema is not just moving table definitions. A production Oracle schema encodes correctness rules — primary keys, unique constraints, foreign keys, check constraints, and indexes — that together enforce the data invariants the application relies on. Migrating these correctly, in the right order, with the right PostgreSQL equivalents, is what distinguishes a schema migration from a table copy.

This chapter covers the constraint and index types found in Oracle schemas, what they map to in PostgreSQL, the behavioral differences that matter, and the ordering constraints that govern when each piece of DDL can be applied.

6.1 Primary Keys

Oracle primary keys are implemented as a unique index plus a NOT NULL constraint on the key columns. When you declare a primary key in Oracle, Oracle creates a unique B-tree index named with the constraint name (or a system-generated name like SYS_C007890).

-- Oracle
CREATE TABLE account (
  account_id NUMBER NOT NULL,
  CONSTRAINT pk_account PRIMARY KEY (account_id)
);

-- Oracle data dictionary
SELECT index_name, uniqueness FROM dba_indexes
WHERE table_name = 'ACCOUNT';
-- Returns: PK_ACCOUNT, UNIQUE

PostgreSQL primary keys work the same way: a unique index plus NOT NULL. The syntax is equivalent:

-- PostgreSQL
CREATE TABLE account (
  account_id INTEGER NOT NULL,
  CONSTRAINT pk_account PRIMARY KEY (account_id)
);

The differences that matter in migration:

Composite primary keys and ordering: PostgreSQL, like Oracle, supports composite primary keys. The column order in the key definition determines the leading index column, which affects query plans. Migration must preserve the column order of composite primary keys, not alphabetize or reorder them.

Deferred primary key constraints: Oracle supports DEFERRABLE INITIALLY DEFERRED primary keys, which means uniqueness is checked at transaction commit rather than at statement execution. PostgreSQL also supports deferrable constraints but does not support deferred primary key violations — a statement that violates a primary key fails immediately regardless of deferral mode. This difference matters for applications that temporarily violate a primary key within a transaction (inserting a duplicate, then deleting the original).

Product note: Pulsaride Transform scans for deferrable primary key constraints during assessment and emits a DEFERRABLE_PK finding. These require application-level analysis to determine whether the deferral is load-bearing. The most common pattern is bulk data loading scripts that use deferred constraints as a performance optimization — these can usually be replaced with PostgreSQL's SET CONSTRAINTS ALL DEFERRED at the session level or restructured to avoid the temporary violation.

6.2 Unique Constraints and Unique Indexes

Oracle distinguishes between a unique constraint (declared in the table definition) and a unique index (created independently). In Oracle, a unique constraint is enforced by a unique index. A standalone CREATE UNIQUE INDEX creates enforcement identical to a constraint but without a named constraint that can be referenced in foreign keys.

-- Oracle: constraint style
ALTER TABLE account ADD CONSTRAINT uq_account_email UNIQUE (email);

-- Oracle: index style (same enforcement, no named constraint)
CREATE UNIQUE INDEX uq_account_email ON account (email);

In PostgreSQL, CREATE UNIQUE INDEX also creates a unique index without a constraint. However, foreign keys in PostgreSQL can only reference columns that have a PRIMARY KEY or UNIQUE constraint — not a standalone unique index. This is the same restriction as Oracle, but it matters during migration when the source has a unique index (not a constraint) that a foreign key references.

PostgreSQL workaround:

-- Add the constraint using the existing index (PostgreSQL 9.x+ syntax)
ALTER TABLE account ADD CONSTRAINT uq_account_email UNIQUE USING INDEX uq_account_email;

This promotes the index to a constraint without rebuilding the index.

NULL in Unique Indexes

Oracle treats NULL as equal-to-NULL for unique index purposes: a unique index on a nullable column allows at most one NULL. PostgreSQL treats NULL as not equal to anything, including another NULL: a unique index on a nullable column allows multiple NULLs.

-- Oracle: second NULL insert fails
INSERT INTO account (account_id, email) VALUES (1, NULL);
INSERT INTO account (account_id, email) VALUES (2, NULL);
-- ORA-00001: unique constraint violated

-- PostgreSQL: second NULL insert succeeds
INSERT INTO account (account_id, email) VALUES (1, NULL);
INSERT INTO account (account_id, email) VALUES (2, NULL);
-- Both succeed — PostgreSQL NULLs are distinct in unique indexes

This behavioral difference can cause reconciliation failures after migration: the source Oracle table has at most one NULL per unique column; the migrated PostgreSQL table could accumulate multiple NULLs if the application inserts them post-migration.

Product note: Pulsaride Transform detects nullable columns that participate in unique constraints/indexes and emits a NULLABLE_UNIQUE_SEMANTICS_DIFFERENCE finding. The reconciliation step validates that NULL counts in unique columns match between source and target. If the application logic relies on Oracle's "one NULL only" enforcement, a PostgreSQL partial unique index achieves the same behavior:

-- PostgreSQL: enforce uniqueness only among non-NULL values (matching Oracle behavior)
CREATE UNIQUE INDEX uq_account_email_non_null
  ON account (email)
  WHERE email IS NOT NULL;

6.3 Foreign Keys

Foreign key migration requires dependency-aware ordering. If table B has a foreign key referencing table A, then A must exist before B's foreign key constraint can be applied. In a schema with hundreds of tables and cross-table foreign keys, the correct order is not obvious.

Product note: Pulsaride Transform builds a dependency graph from Oracle's DBA_CONSTRAINTS and DBA_CONS_COLUMNS views during assessment. The DDL generation step produces a sorted DDL script where tables are created in dependency order. Foreign key constraints are applied in a second pass after all tables exist, which sidesteps circular dependency issues at the table-creation level.

ON DELETE behavior

Oracle foreign keys support:

  • ON DELETE CASCADE — delete child rows when parent is deleted
  • ON DELETE SET NULL — set FK column to NULL when parent is deleted
  • No ON DELETE clause — prevent parent deletion if children exist (this is Oracle's default, equivalent to PostgreSQL's NO ACTION / RESTRICT)

PostgreSQL supports the same options plus ON DELETE SET DEFAULT. Migration preserves the Oracle ON DELETE behavior by mapping it directly. The one difference: Oracle's default (no clause) behaves like RESTRICT — the parent delete is rejected at statement time. PostgreSQL's NO ACTION defers the check to end-of-statement (not end-of-transaction in the default non-deferred case), which is effectively the same for non-deferred constraints.

Deferrable Foreign Keys

Oracle supports DEFERRABLE INITIALLY DEFERRED and DEFERRABLE INITIALLY IMMEDIATE foreign keys. These are used in two real scenarios:

  1. Bulk loading with circular references: Tables A and B reference each other. Loading A first requires B's rows to exist; loading B first requires A's rows. With deferred FKs, you load both and let the check happen at commit.

  2. Parent-child update reordering: An application moves a child row from one parent to another by inserting the new parent, updating the child, then deleting the old parent. With deferred FKs, the intermediate state (child pointing to non-existent new parent) is allowed until commit.

PostgreSQL supports deferrable foreign keys fully. The DDL syntax is the same:

-- PostgreSQL deferrable foreign key
ALTER TABLE order_line
  ADD CONSTRAINT fk_order_line_order
  FOREIGN KEY (order_id)
  REFERENCES order_header (order_id)
  DEFERRABLE INITIALLY DEFERRED;

Migration preserves deferral settings from Oracle. Pulsaride Transform logs each deferrable FK as a DEFERRABLE_FK note in the assessment report.

6.4 Check Constraints

Oracle check constraints can reference any column in the table row. They are evaluated per-row and can include most SQL expressions. Common patterns:

-- Oracle check constraints
ALTER TABLE account ADD CONSTRAINT chk_account_status
  CHECK (status IN ('ACTIVE', 'SUSPENDED', 'CLOSED'));

ALTER TABLE order_header ADD CONSTRAINT chk_positive_amount
  CHECK (total_amount >= 0);

ALTER TABLE employee ADD CONSTRAINT chk_salary_range
  CHECK (salary BETWEEN 0 AND 999999.99);

PostgreSQL check constraints work identically. These three examples migrate without change (after lowercase normalization).

The Oracle check constraint behavior that does NOT migrate directly:

Checks that reference other tables: Oracle does not allow check constraints that reference other tables. Neither does PostgreSQL. Not a migration issue.

Calls to user-defined functions in checks: Oracle allows this, and so does PostgreSQL. However, the function must exist before the constraint can be applied, and it must have been migrated separately. The DDL ordering must account for this.

Disabling check constraints: Oracle allows DISABLE NOVALIDATE — a check constraint that is defined but not enforced. This is used to describe data quality that the application enforces but the database does not. PostgreSQL has no equivalent — constraints are either enforced or absent. Migration must decide: enforce the constraint (may fail if existing data violates it) or omit it (loses the documentation value).

Product note: Pulsaride Transform maps DISABLE NOVALIDATE check constraints to a pre-validation step. During full load, the product runs the check constraint expression as a query against the source data before the constraint is created on the target. Rows that fail are logged as CONSTRAINT_VIOLATION rows in the staging ledger. The operator decides whether to fix the source data, adjust the constraint, or exclude the constraint from the migration.

6.5 Index Types in Oracle and Their PostgreSQL Equivalents

Oracle supports multiple index types with different performance characteristics. PostgreSQL also supports multiple index types. The mapping is not one-to-one.

B-Tree Indexes

Oracle's default index type. PostgreSQL's default index type. Migrates directly.

-- Oracle
CREATE INDEX idx_account_email ON account (email);

-- PostgreSQL (identical syntax)
CREATE INDEX idx_account_email ON account (email);

Function-Based Indexes (Oracle) → Indexes on Expressions (PostgreSQL)

Oracle supports indexes on expressions, commonly called function-based indexes:

-- Oracle: index on UPPER(email) for case-insensitive searches
CREATE INDEX idx_account_upper_email ON account (UPPER(email));

PostgreSQL supports the same concept with identical syntax:

-- PostgreSQL
CREATE INDEX idx_account_upper_email ON account (UPPER(email));

For the index to be used by the query optimizer, the query must use the same expression:

-- Uses the index
SELECT * FROM account WHERE UPPER(email) = 'USER@EXAMPLE.COM';

-- Does NOT use the index
SELECT * FROM account WHERE email = 'user@example.com';

Oracle function-based indexes require DETERMINISTIC functions. PostgreSQL index expressions require IMMUTABLE functions. The assessment scanner checks that Oracle function-based indexes reference deterministic functions and maps them to PostgreSQL with the same expression.

Bitmap Indexes (Oracle) → Not Available in PostgreSQL

Oracle bitmap indexes are designed for low-cardinality columns (status codes, boolean-like values, type codes) in data warehouse workloads. They store a bitmap per distinct value and support fast set operations.

PostgreSQL does not have a bitmap index type. The PostgreSQL query planner can dynamically build bitmap indexes in memory from multiple B-tree indexes during query execution (the "bitmap scan" plan node), but there is no persistent bitmap index structure.

For low-cardinality columns where Oracle would use a bitmap index, the PostgreSQL alternatives are:

  1. B-tree index on the column — effective when the column has at most a few hundred distinct values and queries are selective
  2. Partial indexes — effective when queries frequently filter for a specific value
  3. GIN index on an array column — for multi-value scenarios
  4. No index — if the table is small enough for sequential scans to dominate
-- Oracle bitmap index — no direct PostgreSQL equivalent
CREATE BITMAP INDEX idx_account_status ON account (status_code);

-- PostgreSQL alternatives:
-- Option 1: B-tree (simple)
CREATE INDEX idx_account_status ON account (status_code);

-- Option 2: Partial (if 'ACTIVE' is the selective value)
CREATE INDEX idx_account_status_active ON account (account_id)
  WHERE status_code = 'ACTIVE';

Product note: Pulsaride Transform converts Oracle bitmap indexes to B-tree indexes during DDL generation and emits a BITMAP_INDEX_CONVERTED finding for each one. The finding notes the original column cardinality (obtained from DBA_COLUMNS.NUM_DISTINCT) and recommends a partial index when cardinality is below 10 and one value accounts for more than 80% of rows.

Reverse Key Indexes (Oracle)

Oracle reverse key indexes store the bytes of the index key in reverse order to distribute sequential inserts across the index tree and reduce hot-block contention. Used in Oracle RAC environments or with SEQUENCE-based keys.

PostgreSQL has no reverse key index. The problem they solve (hot-block contention from sequential inserts) is addressed differently in PostgreSQL: PostgreSQL's B-tree implementation uses page-level locking rather than Oracle's block-level locking, and sequential inserts into a B-tree in PostgreSQL are much less likely to cause hot-block contention.

Product note: Pulsaride Transform drops reverse key indexes during migration and emits a REVERSE_KEY_INDEX_DROPPED finding with a note explaining why they are unnecessary in PostgreSQL.

Composite Index Column Order

The column order in a composite index determines which queries can use it. A composite index on (a, b, c) supports queries filtering on a, a + b, or a + b + c, but not b alone or c alone.

Oracle and PostgreSQL share this behavior. Migration must preserve composite index column order. A naive alphabetical sort of column names when generating CREATE INDEX statements would destroy index effectiveness for queries that depend on the leading column.

6.6 DDL Ordering: The Creation Sequence

The sequence in which DDL is applied to the target database matters. Constraints can only be applied after their referenced objects exist. The correct sequence:

  1. Create schemas (PostgreSQL) / users (Oracle → PostgreSQL schema mapping)
  2. Create sequences and identity columns
  3. Create tables without constraints (columns and data types only)
  4. Load data (full load)
  5. Create primary key constraints and unique constraints
  6. Create non-unique indexes
  7. Create foreign key constraints (after all tables exist)
  8. Create check constraints
  9. Create function-based indexes (after functions are migrated)
  10. Enable triggers (after all data is loaded and constraints are satisfied)

Steps 5–9 are deferred until after data load (step 4) for performance reasons. Applying constraints before loading data means every inserted row is constraint-checked individually. Deferring constraints and indexes until after the full load, then building them in bulk using CREATE INDEX CONCURRENTLY or a plain CREATE INDEX (on a table with no live readers yet), is significantly faster.

Product note: Pulsaride Transform generates two DDL scripts: schema-pre-load.sql (tables and sequences, no constraints or indexes) and schema-post-load.sql (all constraints and indexes). The full load step runs against the pre-load schema. The post-load DDL is applied after the full load completes and before CDC begins. This ordering is enforced by the pipeline.

6.7 Example: The ORDER_LINE Constraint Cascade

A retail schema has three tables with a dependency chain:

-- Oracle source schema
CREATE TABLE product (
  product_id  NUMBER PRIMARY KEY,
  sku         VARCHAR2(50),
  CONSTRAINT uq_product_sku UNIQUE (sku)
);

CREATE TABLE order_header (
  order_id      NUMBER PRIMARY KEY,
  customer_id   NUMBER NOT NULL,
  order_date    DATE NOT NULL,
  status        VARCHAR2(20) DEFAULT 'PENDING',
  CONSTRAINT chk_order_status CHECK (status IN ('PENDING', 'SHIPPED', 'CANCELLED'))
);

CREATE TABLE order_line (
  line_id       NUMBER PRIMARY KEY,
  order_id      NUMBER NOT NULL,
  product_id    NUMBER NOT NULL,
  quantity      NUMBER NOT NULL,
  unit_price    NUMBER(12,2) NOT NULL,
  CONSTRAINT fk_ol_order   FOREIGN KEY (order_id)   REFERENCES order_header (order_id) ON DELETE CASCADE,
  CONSTRAINT fk_ol_product FOREIGN KEY (product_id) REFERENCES product (product_id),
  CONSTRAINT chk_positive_qty   CHECK (quantity > 0),
  CONSTRAINT chk_positive_price CHECK (unit_price > 0)
);

CREATE BITMAP INDEX idx_order_status ON order_header (status);
CREATE INDEX idx_ol_order_id ON order_line (order_id);

Pulsaride Transform processes this and produces:

Pre-load DDL:

-- schema-pre-load.sql
CREATE TABLE product (
  product_id  INTEGER NOT NULL,
  sku         TEXT
);

CREATE TABLE order_header (
  order_id    INTEGER NOT NULL,
  customer_id INTEGER NOT NULL,
  order_date  DATE NOT NULL,
  status      TEXT DEFAULT 'PENDING'
);

CREATE TABLE order_line (
  line_id    INTEGER NOT NULL,
  order_id   INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  quantity   INTEGER NOT NULL,
  unit_price NUMERIC(12,2) NOT NULL
);

Post-load DDL:

-- schema-post-load.sql
ALTER TABLE product ADD CONSTRAINT pk_product PRIMARY KEY (product_id);
ALTER TABLE product ADD CONSTRAINT uq_product_sku UNIQUE (sku);

ALTER TABLE order_header ADD CONSTRAINT pk_order_header PRIMARY KEY (order_id);
ALTER TABLE order_header ADD CONSTRAINT chk_order_status
  CHECK (status IN ('PENDING', 'SHIPPED', 'CANCELLED'));

ALTER TABLE order_line ADD CONSTRAINT pk_order_line PRIMARY KEY (line_id);
ALTER TABLE order_line ADD CONSTRAINT fk_ol_order
  FOREIGN KEY (order_id) REFERENCES order_header (order_id) ON DELETE CASCADE;
ALTER TABLE order_line ADD CONSTRAINT fk_ol_product
  FOREIGN KEY (product_id) REFERENCES product (product_id);
ALTER TABLE order_line ADD CONSTRAINT chk_positive_qty   CHECK (quantity > 0);
ALTER TABLE order_line ADD CONSTRAINT chk_positive_price CHECK (unit_price > 0);

-- Bitmap → B-tree with BITMAP_INDEX_CONVERTED finding
CREATE INDEX idx_order_status ON order_header (status);
CREATE INDEX idx_ol_order_id ON order_line (order_id);

The assessment report for this schema includes:

  • BITMAP_INDEX_CONVERTED for idx_order_status — note that status has 3 distinct values; recommend considering a partial index for 'PENDING' if that filter is hot
  • No RESERVED_WORD_COLLISION findings (none of the names are reserved)
  • No NULLABLE_UNIQUE_SEMANTICS_DIFFERENCE findings (the unique column sku is NOT NULL implied by the unique constraint behavior — but the DDL does not declare NOT NULL explicitly, which is a separate finding)

The operator reviews the assessment report, approves the DDL, and proceeds to full load.

← Previous

Chapter 5Names, Quoting, and Case Sensitivity

Next →

Chapter 7Sequences, Identity, and Key Generation