Constraint States

About Constraint States

Leave a Comment / Daily DBA Stuff / By /

A Brief Primer: Constraints

Constraints are database objects that help enforce business rules and ensure data integrity. They are typically defined on tables and their columns. They include:

  • Primary Key
  • Not Null
  • Check
  • Foreign Key
  • Unique

I talk a lot more about constraints in a previous post: about database constraints.

What are Constraint States?

In Oracle, it is possible to control whether or not constraints will block bad data from being keyed in and if they should validate already existing data in the table. This can be done using constraint States. These constraint states enforce 2 behaviors of constraints, their enforcement status (either ENABLE or DISABLE) and whether or not they validate existing data (VALIDATE or NOVALIDATE).

These properties yield four distinct constraint states that dictate how Oracle treats new modifications versus existing table data, they include:

  • ENABLE VALIDATE
  • ENABLE NOVALIDATE
  • DISABLE NOVALIDATE
  • DISABLE VALIDATE

1. ENABLE VALIDATE (Default State)

This is the default state of constraints in Oracle if none is specified. In this state, the enforcement status is ENABLE; which means new records entered are checked and cannot violate the constraint. Also, the data validation state is VALIDATE; meaning that all existing data in the column is checked to ensure that it conforms to the constraint definition. If any existing row violates the constraint, then the constraint definition will fail.

For example, if we create a table t1 with one column and insert some duplicate values:

-- create table
SQL> create table t1 (
  2   c1 NUMBER
  3  ) ;

Table created.
...
-- insert some values
  1  INSERT INTO t1 VALUES
  2	(1)
  3    ,(2)
  4    ,(3)
  5    ,(4)
  6    ,(4)
  7*   ,(5)
SQL> /

6 rows created.

SQL> commit ;

Commit complete.
...

Notice the use of the Table values constructor in Oracle Database 26ai!

We cannot define the column c1 as a primary key, an attempt to do this will fail.

-- attempt to add constraint
SQL> 
SQL> 
SQL> alter table t1
  2  add constraint t1_pk PRIMARY KEY (c1) ;
add constraint t1_pk PRIMARY KEY (c1)
               *
ERROR at line 2:
ORA-02437: cannot validate (HR.T1_PK) - primary key violated
Help: https://docs.oracle.com/error-help/db/ora-02437/


SQL>

As seen above, the constraint definition fails because it’s in the default ENABLE VALIDATE state and because two rows have the value 4, the existing data validation fails. If we drop one of the rows with the value 4, we can then define the constraint.

SQL> desc t1
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1						    NUMBER

SQL> 
SQL> select * from t1
  2  ;

	C1
----------
	 1
	 2
	 3
	 4
	 4
	 5

6 rows selected.

SQL> select rowid, c1
  2  from t1 ;

ROWID			   C1
------------------ ----------
AAAR3/AAAAAAAMcAAA	    1
AAAR3/AAAAAAAMcAAB	    2
AAAR3/AAAAAAAMcAAC	    3
AAAR3/AAAAAAAMcAAD	    4
AAAR3/AAAAAAAMcAAE	    4
AAAR3/AAAAAAAMcAAF	    5

6 rows selected.

SQL> 
SQL> delete from t1
  2  where rowid = 'AAAR3/AAAAAAAMcAAD';

1 row deleted.

SQL> 
SQL> commit ;

Commit complete.

SQL> ALTER TABLE t1    
  2  ADD CONSTRAINT t1_pk PRIMARY KEY (c1) ;

Table altered.

SQL> 
SQL> desc t1
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1					   NOT NULL NUMBER

We can also confirm the constraint state by querying the STATUS and VALIDATED columns of the USER[ALL][DBA]_CONSTRAINTS view.

SQL> 
SQL> col constraint_name for a20
SQL> select constraint_name, status, validated
  2  from user_constraints ;

CONSTRAINT_NAME      STATUS   VALIDATED
-------------------- -------- -------------
T1_PK		     ENABLED  VALIDATED

SQL>

In this constraint state, any operation on a row that causes it to violate the rule will fail. The table is guaranteed to be 100% compliant to the constraint definition.

SQL> INSERT INTO t1
  2  VALUES (2) ;
INSERT INTO t1
*
ERROR at line 1:
ORA-00001: unique constraint (HR.T1_PK) violated on table HR.T1 columns (C1)
ORA-03301: (ORA-00001 details) row with column values (C1:2) already exists
Help: https://docs.oracle.com/error-help/db/ora-00001/

2. ENABLE NOVALIDATE

Inthis state, the constraint is checked and enforced strictly for any DML operation on the table going forward. Existing data is not checked and validated. This state is often used when applying a new business rule to a table that already contains bad data but cannot be cleaned up immediately.

For example, if we drop the constraint on the table t1 and insert rows with the same values as the existing ones:

SQL> ALTER TABLE t1
  2  DROP CONSTRAINT t1_pk ;

Table altered.

SQL> INSERT INTO t1 
  2  SELECT * FROM t1 ;

5 rows created.

SQL> 
SQL> select * from t1 ;

	C1
----------
	 1
	 2
	 3
	 4
	 5
	 1
	 2
	 3
	 4
	 5

10 rows selected.

SQL> commit ;

Commit complete.

and we attempt to create the primary key again in the ENABLE NOVALIDATE state:

SQL> 
SQL> ALTER TABLE t1
  2  ADD CONSTRAINT t1_pk PRIMARY KEY (c1) ENABLE NOVALIDATE ;
ADD CONSTRAINT t1_pk PRIMARY KEY (c1) ENABLE NOVALIDATE
               *
ERROR at line 2:
ORA-02437: cannot validate (HR.T1_PK) - primary key violated
Help: https://docs.oracle.com/error-help/db/ora-02437/

SQL>

As shown above, despite specifying ENABLE NOVALIDATE, the constraint definition fails. This is because primary key and unique constraints by default attempt to build unique indexes to support the constraint. In order to define ENABLE NOVALIDATE on the primary key column, we must manually create a non-unique index.

This can be done as part of the constraint definition as shown below:

SQL> l
  1  ALTER TABLE T1
  2  ADD CONSTRAINT t1_pk PRIMARY KEY (c1)
  3  USING INDEX ( CREATE INDEX t1_pk ON t1(c1) )
  4* ENABLE NOVALIDATE
SQL> /

Table altered.

For check or foreign key constraints, unique indexes are not required:

SQL> ALTER TABLE T1 
  2  ADD CONSTRAINT t1_ck1 CHECK (
  3   c1 < 5 
  4  )
  5  ENABLE NOVALIDATE ;

Table altered.

Despite both these constraints defined on the table, there is existing data in the table violating the rules the constraints enforce.

SQL> SELECT * FROM T1 ;

	C1
----------
	 1
	 2  --* repeating values in a pk column
	 3
	 4
	 5 -- value of 5 violates check constraint
	 1
	 2 --* repeating values in a pk column
	 3
	 4
	 5

10 rows selected.

If we attempt to insert a value of 6 in the table post constraint definition, we get an error:

SQL> INSERT INTO t1
  2  VALUES (6) ;
INSERT INTO t1
*
ERROR at line 1:
ORA-02290: check constraint (HR.T1_CK1) violated
Help: https://docs.oracle.com/error-help/db/ora-02290/

DML operations on the table are still allowed on existing rows that violate the constraints, provided the resulting data does not introduce a new violation. For example, two rows in the table t1 have a value of 5, which violates both the primary key and check constraint. Attempting to update one or both of the rows to 6 will lead to results that still violate both constraints and thus fails:

SQL> update t1
  2  set c1 = 6 
  3  where c1 = 5 ;
update t1
*
ERROR at line 1:
ORA-02290: check constraint (HR.T1_CK1) violated
Help: https://docs.oracle.com/error-help/db/ora-02290/

No further update on the table can violate any of the constraints:

SQL> update t1
  2  set c1 = 1
  3  where c1 = 2 ;
update t1
*
ERROR at line 1:
ORA-00001: unique constraint (HR.T1_PK) violated on table HR.T1 columns (C1)
Help: https://docs.oracle.com/error-help/db/ora-00001/

We can confirm the constraint states:

SQL> select constraint_name, status, validated
  2  from user_constraints ;

CONSTRAINT_NAME      STATUS   VALIDATED
-------------------- -------- -------------
T1_PK		     ENABLED  NOT VALIDATED
T1_CK1		     ENABLED  NOT VALIDATED

Notice the VALIDATED column shows a value of NOT VALIDATED.

3. DISABLE NOVALIDATE

With this state, the existing data is not checked and further DML on the table will not error out if it violates the constraint. The constraint is completely inactive. Database developers use this state to optimize performance during large batch data loads, as it skips all validation overhead.

For example, we drop both constraints on t1:

SQL> ALTER TABLE t1
  2  DROP CONSTRAINT t1_pk ;

Table altered.

SQL> 
SQL> ALTER TABLE t1
  2  DROP CONSTRAINT t1_ck1 ;

Table altered.

We then add a new check constraint in the DISABLE NOVALIDATE state:

SQL> ALTER TABLE t1
  2  ADD constraint t1_ck1 CHECK (
  3    c1 < 5
  4  )
  5  DISABLE NOVALIDATE ;

Table altered.

We are able to insert rows that violate the constraint:

SQL> INSERT INTO t1
  2  VALUES (6)
  3        ,(7)
  4        ,(8)
  5        ,(9)
  6        ,(10)
  7  ;

5 rows created.

SQL> 
SQL> commit ;

Commit complete.

As seen above, the t1_ck1 check constraint by definition should prevent adding any rows with values that are greater than or equal to 5. However, the DML after defining the constraint adds several rows that violate that rule successfully. This is because the constraint is in the DISABLE NOVALIDATE state.

 SQL> select constraint_name, status, validated
  2  from user_constraints ;

CONSTRAINT_NAME      STATUS   VALIDATED
-------------------- -------- -------------
T1_CK1		     DISABLED NOT VALIDATED

4. DISABLE VALIDATE

This state validates existing data in the table to ensure it complies with the constraint definition. However, DML that affects the constrained column is blocked entirely. When this is specified for primary key and unique constraints, the table is effectively “read-only”. Because new inserts or updates to the column are locked out, it is often utilized for creating a read-only table or altering index structures without compromising data integrity.

For example, we drop the existing constraints on table t1 and delete all rows:

SQL> ALTER TABLE t1
  2  DROP CONSTRAINT t1_ck1 ;

Table altered.
SQL> DELETE FROM T1 ;

15 rows deleted.

We then insert rows into column c1 with values 1 through 10:

SQL> INSERT INTO T1
  2  SELECT rownum
  3  FROM DUAL
  4  CONNECT BY LEVEL <= 10 ;

10 rows created.

SQL> SELECT * FROM t1 ;

	C1
----------
	 1
	 2
...
	10

10 rows selected.

SQL> COMMIT ;

Commit complete.

We then define a primary key constraint on column c1 in the DISABLE VALIDATE state:

SQL> ALTER TABLE t1         
  2  ADD CONSTRAINT t1_pk PRIMARY KEY (c1) 
  3  DISABLE VALIDATE ;

Table altered.

If we attempt any DML on the table t1, we get an ORA-25128 error.

For example, an insert:

SQL> INSERT INTO T1
  2  VALUES (11) ;
INSERT INTO T1
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (HR.T1_PK) disabled
and validated
Help: https://docs.oracle.com/error-help/db/ora-25128/

an update:

SQL> UPDATE t1   
  2  SET  c1 = 11
  3  WHERE c1 = 10 ;
UPDATE t1
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (HR.T1_PK) disabled
and validated
Help: https://docs.oracle.com/error-help/db/ora-25128/

or a delete:

SQL> DELETE FROM t1 ;
DELETE FROM t1
*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (HR.T1_PK) disabled
and validated
Help: https://docs.oracle.com/error-help/db/ora-25128/

all fail due to the constraint state.

SQL> select constraint_name, status, validated
  2  from user_constraints ;

CONSTRAINT_NAME      STATUS   VALIDATED
-------------------- -------- -------------
T1_PK		     DISABLED VALIDATED

The only way to perform any further DML on this table is to drop the constraint or alter its state.

Altering Constraint States

Constraint states can be altered in Oracle using the ALTER TABLE... MODIFY CONSTRAINT command. For example, we can modify the t1_pk constraint and change its state to ENABLE VALIDATE.

SQL> ALTER TABLE t1
  2  MODIFY CONSTRAINT t1_pk ENABLE VALIDATE ;

Table altered.

Once this is done we are able to perform DML on the table once more:

SQL> INSERT INTO t1
  2  VALUES (11) ;

1 row created.

SQL> COMMIT ;

Commit complete.

Other Constraint Properties

In addition to the various constraint states, there are also other attributes of constraints that affect their behavior and can be useful for certain scenarios. They include the RELY/NORELY and the deferrable attributes. Let’s examine these briefly:

RELY/NORELY

In Oracle Database, the RELY and NORELY parameters determine whether the optimizer can consider a constraint the NOVALIDATE state for query rewrite. They can be specified only when modifying an existing constraint using the syntax:

 ALTER TABLE ... MODIFY constraint

From this post on Dani Schnider’s blog, it’s encouraged to define data warehouse foreign key constraints in the DISABLE NOVALIDATE RELY mode when the ETL jobs are guaranteed to preserve data integrity as there are performance benefits to this implementation.

Also, declaring Primary Keys or Foreign Keys on database Views helps map relationships. However, Views cannot enforce constraints. They are always DISABLE NOVALIDATE. Adding RELY allows Oracle to optimize queries against those views anyway.

Obviously, the drawback of using RELY is that if your data breaks the rule (e.g., you have a RELY Foreign Key pointing to a record that doesn’t exist in the parent table), your queries might return wrong data or incorrect aggregations without raising any errors.

Deferring Constraints

In Oracle, it’s also possible to delay the validation of constraints until the end of a transaction. This means every single DML statement would not be validated until the transaction ends. By default, constraints are not deferrable; i.e. The database checks the constraint at the end of each statement. If the constraint is violated, then the statement rolls back.

A deferrable constraint permits a transaction to use the SET CONSTRAINT clause to defer checking of this constraint until a COMMIT statement is issued. If you make some changes to the database that might violate the constraint, then this setting effectively enables you to disable the constraint validation until all changes are complete.

You can specify either of the following attributes:

  • DEFERRABLE INITIALLY IMMEDIATE : The database checks the constraint immediately after each statement executes. If the constraint is violated, then the database rolls back the statement.
    or
  • DEFERRABLE INITIALLY DEFERRED : The database checks the constraint when a COMMIT is issued. If the constraint is violated, then the database rolls back the transaction.

Deferring Constraints can be useful for a transaction that may temporarily break data integrity constraints in intermediate steps but restore valid logic before it completes. This post by Chris Saxon on Oracle blogs shows an example use case of deferring constraints and assertions. Swapping the ranks of two students is another valid example where deferring constraints might be useful. This post talks about Deferred Constraints in Oracle.

Conclusion

As shown in this post, constraints can exist in many different states which can be helpful for a variety of different purposes. It ultimately comes down to specifics of the use case on whether to use any of these states or not. In my experience, 99% of the time the defaults are what are required especially for OLTP applications. Knowledge of the different states is however still important and relevant as it could help save space on indexes, improve ETL job performance and handle complex transactions that temporarily violate data integrity. Most importantly, care must be taken to ensure that data does not get corrupted as for most businesses, correct data is more important than storage savings or faster performance. Data integrity is almost always Key, it’s the reason you have a RDBMS anyways!

References

Leave a Comment

Your email address will not be published. Required fields are marked *

Related Posts