Tag: constraints

Deferrable Constraints: When Database Rules Can Wait

Database constraints are usually quite impatient. As soon as a statement violates a unique key, foreign key or another constraint, the database rejects it.

Most of the time this is exactly what we want. But sometimes an intermediate state is invalid even though the final state of the transaction is perfectly valid.

Consider a table that stores the order of items:

CREATE TABLE tasks (
    id       INTEGER PRIMARY KEY,
    position INTEGER NOT NULL,
    CONSTRAINT tasks_position_unique UNIQUE (position)
);

Suppose it contains:

id | position
---+---------
 1 | 1
 2 | 2

Now we want to swap the two positions. The obvious approach does not work:

UPDATE tasks SET position = 2 WHERE id = 1;
UPDATE tasks SET position = 1 WHERE id = 2;

The first statement already violates the unique constraint because task 2 still has position 2. We could work around this by assigning a temporary value:

UPDATE tasks SET position = -1 WHERE id = 1;
UPDATE tasks SET position = 1  WHERE id = 2;
UPDATE tasks SET position = 2  WHERE id = 1;

But this is really an implementation detail leaking into our data manipulation. What we actually want to express is much simpler:

The positions have to be unique when the transaction is finished. We do not care about temporary duplicates while changing them.

This is what deferrable constraints are for.

Deferring a constraint

Both PostgreSQL and Oracle support the SQL keywords DEFERRABLE, INITIALLY IMMEDIATE and INITIALLY DEFERRED. A deferrable constraint can be switched from immediate checking to checking at the end of the transaction.

We can define our constraint like this:

CREATE TABLE tasks (
    id       INTEGER PRIMARY KEY,
    position INTEGER NOT NULL,
    CONSTRAINT tasks_position_unique
        UNIQUE (position)
        DEFERRABLE INITIALLY IMMEDIATE
);

INITIALLY IMMEDIATE means that it behaves like an ordinary constraint by default. The important difference is that a transaction is allowed to defer it explicitly.

Before performing our swap we can write:

SET CONSTRAINTS tasks_position_unique DEFERRED;
UPDATE tasks SET position = 2 WHERE id = 1;
UPDATE tasks SET position = 1 WHERE id = 2;
COMMIT;

After the first UPDATE the table temporarily contains the position 2 twice. This is allowed because the constraint has been deferred. After the second UPDATE all positions are unique again. When the transaction is committed, the constraint succeeds.

If we forgot the second update, the COMMIT would fail instead. So the constraint has not been disabled. Its check has merely been postponed.

Initially immediate or initially deferred?

A deferrable constraint has two useful default modes.

DEFERRABLE INITIALLY IMMEDIATE

means that the database normally checks the constraint immediately, but individual transactions can defer it.

DEFERRABLE INITIALLY DEFERRED

means that the database normally waits until the transaction is committed.

For most application tables INITIALLY IMMEDIATE is probably easier to reason about. Constraint violations still occur close to the statement that caused them, and deferral is explicitly enabled only for operations that need it.

You can also defer all deferrable constraints in a transaction:

SET CONSTRAINTS ALL DEFERRED;

Both PostgreSQL and Oracle support this form.

PostgreSQL and Oracle

The basic mechanism looks remarkably similar in PostgreSQL and Oracle. In both databases, constraints are NOT DEFERRABLE by default. If you want to change their checking mode during a transaction, they have to be created as DEFERRABLE. There are some differences in the details, though.

PostgreSQL currently allows deferral for UNIQUE, PRIMARY KEY, foreign key and EXCLUDE constraints. CHECK and NOT NULL constraints are always checked immediately.

Oracle’s model is somewhat broader. Oracle also supports deferrable constraints such as NOT NULL constraints. Its documentation explicitly describes a deferrable NOT NULL constraint whose violation is detected when the transaction is committed.

Another use case: Foreign keys

Unique constraints are an easy way to demonstrate the problem, but foreign keys are probably the more familiar use case.

Imagine importing a set of objects that reference each other. The final object graph is consistent, but the input format does not guarantee an insertion order that satisfies all foreign keys along the way. Without deferral, the application has to determine the correct ordering itself.

With deferrable foreign keys, all objects can be inserted first and referential integrity can be checked once the transaction is complete. This is particularly useful for bulk imports, cyclic relationships and more complicated restructuring operations.

Deferring vs. Disabling

There is an important conceptual difference between deferring a constraint and disabling it. When a constraint is disabled, invalid data may remain in the database. When a constraint is deferred, invalid data may only exist as an intermediate state inside a transaction.

Before that transaction can successfully finish, the database rules must be satisfied again. That makes deferrable constraints a nice fit for operations where the individual steps temporarily break an invariant, while the operation as a whole preserves it.

Deferred Constraints in Oracle DB

Foreign key constraints are like rules in your Oracle database that make sure data is linked properly between tables. For example, you can’t add an order for a customer who doesn’t exist – that’s the kind of thing a foreign key will stop. They help enforce data integrity by ensuring that relationships between tables remain consistent. But hidden in the toolbox of Oracle Database is a lesser-known trick: deferred foreign key constraints.

What Are Deferred Constraints?

By default, when you insert or update data that violates a foreign key constraint, Oracle will throw an error immediately. That’s immediate constraint checking.

But with deferred constraints, Oracle lets you temporarily violate a constraint during a transaction – as long as the constraint is satisfied by the time the transaction is committed.

Here’s how you make a foreign key deferrable:

ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id)
  REFERENCES customers(customer_id)
  DEFERRABLE INITIALLY DEFERRED;

That last part – DEFERRABLE INITIALLY DEFERRED – is the secret sauce. Now, the constraint check for fk_orders_customer is deferred until the COMMIT.

Use Cases

Let’s look at a few situations where this is really helpful.

One use case are circular references between tables. Say you have two tables: one for employees, one for departments. Each employee belongs to a department. But each department also has a manager – who is an employee. You end up in a “chicken and egg” situation. Which do you insert first? With deferred constraints, it doesn’t matter – you can insert them in any order, and Oracle will only check everything after you’re done.

Another use case is the bulk import of data. If you’re importing a bunch of data (like copying from another system), it can be really hard to insert things in the perfect order to keep all the foreign key rules happy. Deferred constraints let you just insert everything, then validate it all at the end with one COMMIT.

Deferred constraints also help when dealing with temporary incomplete data: Let’s say your application creates a draft invoice before all the customer info is ready. Normally, this would break a foreign key rule. But if the constraint is deferred, Oracle gives you time to finish adding all the pieces before checking.

Caution

Using deferred constraints recklessly can lead to runtime surprises. Imagine writing a huge batch job that appears to work fine… until it crashes at COMMIT with a constraint violation error – rolling back the entire transaction. So only defer constraints when you really need to.

One last tip

If you want to check if a constraint is deferrable in your database you can use the following SQL query:

SELECT constraint_name, deferrable, deferred
  FROM user_constraints
 WHERE table_name='ORDERS';

Inline and Implicit Foreign Key Constraints in SQL

Foreign key constraints are a key part of database design, ensuring that relationships between tables are consistent and reliable. They create a relationship between two tables, ensuring that data matches across them. For example, a column in an “Orders” table (like CustomerID) might reference a column in a “Customers” table. This guarantees that every order belongs to a valid customer.

In earlier versions of SQL systems, defining foreign key constraints often required separate ALTER TABLE statements after the table was created:

CREATE TABLE Orders (
  OrderID    INT PRIMARY KEY,
  CustomerID INT NOT NULL,
  OrderDate  DATE
);

ALTER TABLE Orders
ADD CONSTRAINT FK_Customer FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID);

This two-step process was prone to errors and required careful management to ensure all constraints were applied correctly.

Inline Foreign Key Constraints

Most of the popular SQL database systems – PostgreSQL, Oracle, SQL Server, and MySQL since version 9.0, released in July 2024 – now support inline foreign key constraints. This means you can define the relationship directly in the column definition, making table creation easier to read:

CREATE TABLE Orders (
  OrderID    INT PRIMARY KEY,
  CustomerID INT NOT NULL REFERENCES Customers(CustomerID),
  OrderDate  DATE
);

Fortunately, this syntax is the same across these systems. However, MySQL 9 additionally supports implicit foreign key constraints:

CREATE TABLE Orders (
  OrderID    INT PRIMARY KEY,
  CustomerID INT NOT NULL REFERENCES Customers,
  OrderDate  DATE
);

By leaving out the (CustomerID) in the REFERENCES clause it will assume that you want to reference the primary key of the parent table. This syntax is unique to MySQL, and you should avoid it if you need to write SQL DDL statements that works across multiple database systems.

Monitoring data integrity with health checks

An important aspect for systems, which are backed by a database storage, is to maintain data integrity. Most relational databases offer the possibility to define constraints in order to maintain data integrity, usually referential integrity and entity integrity. Typical constraints are foreign key constraints, not-null constraints, unique constraints and primary key constraints.

SQL also provides the CHECK constraint, which allows you to specify a condition on each row in a table:

ALTER TABLE table_name ADD CONSTRAINT
   constraint_name CHECK ( predicate )

For example:

CHECK (AGE >= 18)

However, these check constraints are limited. They can’t be defined on views, they can’t refer to columns in other tables and they can’t include subqueries.

Health checks

In order to monitor data integrity on a higher level that is closer to the business rules of the domain, we have deployed a technique that we call health checks in some of our applications.

These health checks are database queries, which check that certain constraints are met in accordance with the business rules. The queries are usually designed to return an empty result set on success and to return the faulty data records otherwise.

The health checks are run periodically. For example, we use a Jenkins job to trigger the health checks of one of our web applications every couple of hours. In this case we don’t directly query the database, but the application does and returns the success or failure states of the health checks in the response of a HTTP GET request.

This way we can detect problems in the stored data in a timely manner and take countermeasures. Of course, if the application is bug free these health checks should never fail, and in fact they rarely do. We mostly use the health checks as an addition to regression tests after a bug fix, to ensure and monitor that the unwanted state in the data will never happen again in the future.