PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, NOT NULL, DEFERRABLE, and RELY/NOVALIDATE Concepts

Learning outcomes

ServiceHub's tables now use appropriate Oracle data types, but correct types alone cannot guarantee business integrity. A work order can still reference a nonexistent technician, repeat a supposedly unique external key, or contain a status outside the allowed domain. Oracle constraints encode these invariants, but their enforcement, validation, deferral, and index relationships must be understood precisely.

1. Constraints are integrity rules; indexes are access structures

A primary key says “this key is unique and not null.” A unique constraint says that duplicate non-null key combinations are not allowed. A foreign key says child values must match a referenced unique/primary key (subject to null semantics). A check constraint says each row must make its condition true or unknown; NOT NULL forbids nulls. Oracle can create or use indexes to enforce PRIMARY KEY and UNIQUE constraints, but the constraint and index remain different metadata objects with different purposes and lifecycle rules.

DROP TABLE IF EXISTS servicehub_probe_reading PURGE;DROP TABLE IF EXISTS servicehub_probe_asset PURGE;CREATE TABLE servicehub_probe_asset (  asset_id      NUMBER CONSTRAINT pk_sh_probe_asset PRIMARY KEY,  asset_code    VARCHAR2(40 CHAR) CONSTRAINT uq_sh_probe_code UNIQUE,  state         VARCHAR2(12 CHAR) DEFAULT 'ACTIVE' NOT NULL,  CONSTRAINT ck_sh_probe_state CHECK (state IN ('ACTIVE','PAUSED','RETIRED')));CREATE TABLE servicehub_probe_reading (  reading_id NUMBER CONSTRAINT pk_sh_probe_reading PRIMARY KEY,  asset_id   NUMBER NOT NULL,  reading_value NUMBER(8,2),  CONSTRAINT fk_sh_probe_asset    FOREIGN KEY (asset_id)    REFERENCES servicehub_probe_asset(asset_id)    DEFERRABLE INITIALLY IMMEDIATE);

2. Observe constraint state instead of assuming “created = validated”

USER_CONSTRAINTS exposes whether a constraint is enabled, validated, deferrable, deferred, and RELY-marked. These dimensions answer different questions. ENABLE VALIDATE enforces new changes and verifies existing data. ENABLE NOVALIDATE enforces new DML but does not prove that preexisting rows comply. DISABLE NOVALIDATE provides no enforcement guarantee. A RELY-marked NOVALIDATE constraint can be considered for query rewrite under appropriate integrity settings; that is an optimizer assertion, not proof that every row is valid.

SELECT constraint_name, constraint_type, status, validated,       deferrable, deferred, rely, index_nameFROM   user_constraintsWHERE  table_name IN ('SERVICEHUB_PROBE_ASSET','SERVICEHUB_PROBE_READING')ORDER BY table_name, constraint_name;SELECT index_name, uniqueness, statusFROM   user_indexesWHERE  table_name IN ('SERVICEHUB_PROBE_ASSET','SERVICEHUB_PROBE_READING')ORDER BY table_name, index_name;

Notice that foreign keys are not automatically backed by a child-side index just because the constraint exists. Primary/unique enforcement and foreign-key lookup/concurrency needs are different mechanisms.

3. Immediate versus deferred checking changes transaction order, not the final rule

A nondeferrable constraint is checked according to its fixed semantics. A DEFERRABLE constraint can be checked immediately or deferred until transaction end. This is useful for tightly controlled operations that temporarily pass through an inconsistent intermediate ordering but finish in a valid state. It is not permission to commit invalid data.

-- Immediate mode: child-before-parent fails.INSERT INTO servicehub_probe_reading(reading_id, asset_id, reading_value)VALUES (100, 900, 12.5);-- Expected: ORA-02291 integrity constraint violated - parent key not found.SET CONSTRAINTS fk_sh_probe_asset DEFERRED;INSERT INTO servicehub_probe_reading(reading_id, asset_id, reading_value)VALUES (100, 900, 12.5);INSERT INTO servicehub_probe_asset(asset_id, asset_code)VALUES (900, 'LAB-ASSET-900');COMMIT;SET CONSTRAINTS fk_sh_probe_asset IMMEDIATE;

The successful commit proves only that the final transaction state satisfies the deferred foreign key. Use deferral for a documented transactional need, because it shifts failure detection later and complicates debugging when overused.

4. NOVALIDATE and RELY are migration/warehouse tools with sharp edges

During controlled data migrations you may need to begin enforcing a rule for new DML before a historical cleanup is complete. ENABLE NOVALIDATE can support that staged process. However, the dictionary must then be read literally: existing rows have not been proven valid. Adding RELY can let query rewrite trust a NOVALIDATE relationship in suitable integrity modes, so incorrect historical data can lead to incorrect transformed query results. Never mark a relationship RELY merely to make an optimizer transformation possible.

ALTER TABLE servicehub_probe_asset DISABLE CONSTRAINT ck_sh_probe_state;INSERT INTO servicehub_probe_asset(asset_id, asset_code, state)VALUES (901, 'LAB-ASSET-901', 'BROKEN');COMMIT;ALTER TABLE servicehub_probe_asset  ENABLE NOVALIDATE CONSTRAINT ck_sh_probe_state;SELECT constraint_name, status, validated, relyFROM user_constraintsWHERE constraint_name = 'CK_SH_PROBE_STATE';-- New violation is rejected even though the historical bad row remains:INSERT INTO servicehub_probe_asset(asset_id, asset_code, state)VALUES (902, 'LAB-ASSET-902', 'BROKEN');-- Expected constraint violation.-- Repair history, then validate:UPDATE servicehub_probe_asset SET state='PAUSED' WHERE asset_id=901;COMMIT;ALTER TABLE servicehub_probe_asset  ENABLE VALIDATE CONSTRAINT ck_sh_probe_state;

5. Foreign-key indexes are a concurrency design decision

Current Oracle 26ai Concepts documentation states that when a child foreign key is unindexed and a session modifies a referenced parent primary key (for example, delete/update/merge), Oracle can acquire a full table lock on the child. Indexing the child foreign-key columns avoids that full-table locking pattern and also improves many child-by-parent lookups. The design cost is additional index storage and DML maintenance, so “index every foreign key” should still be justified by parent-key modification and query patterns.

CREATE INDEX ix_sh_probe_reading_assetON servicehub_probe_reading(asset_id);SELECT i.index_name, c.column_name, c.column_positionFROM user_indexes iJOIN user_ind_columns c  ON c.index_name = i.index_nameWHERE i.table_name = 'SERVICEHUB_PROBE_READING'ORDER BY i.index_name, c.column_position;

For a real concurrency experiment use two disposable sessions and a tiny probe schema, never a production parent table. Observe locks with administrator-approved dynamic performance views rather than assuming an index removes every possible wait.

6. Deliberately wrong approach: “constraint exists, therefore data is trusted”

An operator sees a foreign key row in USER_CONSTRAINTS and tells a reporting team that orphan rows are impossible. That conclusion is invalid if the constraint is disabled or NOVALIDATE with historical violations. The repair is an evidence check that includes STATUS, VALIDATED, and, where relevant, RELY, followed by explicit data validation before the rule is treated as a guarantee.

SELECT table_name, constraint_name, constraint_type,       status, validated, deferrable, deferred, relyFROM user_constraintsWHERE table_name LIKE 'SERVICEHUB_PROBE_%'ORDER BY table_name, constraint_name;SELECT r.reading_id, r.asset_idFROM servicehub_probe_reading rLEFT JOIN servicehub_probe_asset a ON a.asset_id = r.asset_idWHERE a.asset_id IS NULL;

7. Hands-on lab and cleanup

Run the complete probe schema lifecycle: create the two tables, capture constraint/index metadata, trigger the immediate foreign-key error, perform the valid deferred transaction, demonstrate NOVALIDATE history, repair it, create the FK index, and finish with all constraints ENABLED and VALIDATED. Then clean up only the disposable probe objects.

SELECT constraint_name, status, validatedFROM user_constraintsWHERE table_name IN ('SERVICEHUB_PROBE_ASSET','SERVICEHUB_PROBE_READING')ORDER BY constraint_name;SELECT COUNT(*) AS orphan_countFROM servicehub_probe_reading rWHERE NOT EXISTS (  SELECT 1 FROM servicehub_probe_asset a WHERE a.asset_id=r.asset_id);DROP TABLE IF EXISTS servicehub_probe_reading PURGE;DROP TABLE IF EXISTS servicehub_probe_asset PURGE;

8. Production judgment and next step

Default production posture is simple constraints in ENABLE VALIDATE, named explicitly and backed by indexes only where their enforcement or access/concurrency role justifies them. Use deferral and NOVALIDATE as controlled migration/transaction tools with documented exit criteria. Treat RELY as an optimizer/query-rewrite assertion whose truth must be guaranteed outside the unenforced constraint state. No management pack is required for these core integrity mechanisms.

Lesson 4 turns to identifiers. Constraints can guarantee uniqueness, but they do not explain how a scalable surrogate key should be generated or why gaps in generated numbers are normal.

Authoritative references

• Constraint clause — DEFERRABLE, ENABLE/DISABLE, VALIDATE/NOVALIDATE, RELY semantics
• Data concurrency and consistency — current 26ai locking behavior for indexed/unindexed foreign keys
• Database Development Guide — integrity and application-development guidance
• Oracle AI Database Reference — constraint and index dictionary metadata