Sign Up
Docs
All Guides

Step-by-step Atlas walkthroughs

Databases

Database-specific Atlas guides

Setting up CI/CD

Wire Atlas into your pipeline

ORMs and Frameworks

Integrate Atlas with any ORM

Shift Left Database Security

Roles, permissions, and policies as code

Seed Data as Code

Lookup tables and reference data

Deploying on Kubernetes

Run via the Atlas Operator

Terraform Provider

Manage schemas via Terraform

Database Governance

End-to-end control and compliance

Schema Drift Detection

Block drift before and after deploys

AI-Safe Database Changes

Safe schema changes at machine speed

Modernize Schema Migrations

Apply modern CI/CD to schema changes

Standardize Schema Migrations

One migration tool to rule them all

Database per tenant

Manage thousands of databases as one

PRICING
Sign InSign Up
Back to changelog
New
•2 minute read

Oracle: Deferrable Keys and Constraint State

Oracle primary and unique keys can now be deferrable, reuse an existing index, and carry constraint state, with changes planned in the narrowest form Oracle allows.

Oracle primary and unique keys now support deferrable and index, along with the constraint state attributes enabled, validated, and rely.

Deferrable Keys and USING INDEX

Set deferrable to INITIALLY_DEFERRED or INITIALLY_IMMEDIATE, and use index to reuse an existing index for the key:

schema.hcl
index "PARCELS_ID_IDX" {  unique  = true  columns = [column.ID]}primary_key "PARCELS_PK" {  columns    = [column.ID]  index      = index.PARCELS_ID_IDX  # USING INDEX.  deferrable = INITIALLY_DEFERRED    # or INITIALLY_IMMEDIATE.}
atlas migrate diff
-- create index "PARCELS_ID_IDX" to table: "PARCELS"CREATE UNIQUE INDEX "PARCELS_ID_IDX" ON "APP"."PARCELS" ("ID");-- modify "PARCELS" tableALTER TABLE "APP"."PARCELS" ADD CONSTRAINT "PARCELS_PK" PRIMARY KEY ("ID")  USING INDEX "PARCELS_ID_IDX" DEFERRABLE INITIALLY DEFERRED;

A key and its reused index are inspected as separate objects, so both round-trip, using the constraint's column order rather than the index's. A UNIQUE constraint backed by a non-unique index is inspected the same way, alongside the index it references.

Switching deferrable between INITIALLY_IMMEDIATE and INITIALLY_DEFERRED is planned with an in-place MODIFY CONSTRAINT. Making a key deferrable, or changing its index, requires dropping and re-adding the constraint.

Constraint State

enabled, validated, and rely map to Oracle's constraint state clauses such as ENABLE, NOVALIDATE, and RELY:

schema.hcl
unique "S1_UQ" {  columns   = [column.B]  enabled   = true   # ENABLE (default).  validated = false  # NOVALIDATE.  rely      = true   # RELY.}
atlas migrate diff
-- modify "S1" tableALTER TABLE "APP"."S1" MODIFY CONSTRAINT "S1_UQ" ENABLE NOVALIDATE RELY;

State changes are applied in place without recreating the constraint. Disabled primary and unique keys are inspected the same way as enabled ones, so a key with enabled = false is included in the schema rather than dropped.

Getting Started

Oracle support is part of Atlas Pro. Run atlas login, declare the attributes on the keys you want to manage, and run atlas migrate diff or atlas schema apply. See the Oracle HCL reference for the full list of attributes.

featureoracleprimary-keyuniqueconstraints
Back to changelog