Declarative Integrity Beyond Keys in SQL

12
0

Introduction

A primary key can uniquely identify a product, but it cannot prevent that product from having a negative price. A foreign key can confirm that a referenced record exists, but it cannot ensure that a discount is smaller than the price.

Declarative integrity handles such requirements by defining rules in the database schema. The database checks those rules when data changes, helping different applications follow the same requirements.

What Is Declarative Integrity?

Declarative integrity means stating which data is valid without writing a separate sequence of steps to check every change.

For example:

CHECK (Price >= 0)

Here, >= means “greater than or equal to.” The rule prevents a negative price.

The database performs the check when a relevant row is inserted or updated. Application validation remains useful for friendly messages, but database constraints protect data even when changes come from another application or a direct SQL statement.

Example Table and Business Rules

Consider a Product table containing product identifiers, names, prices, discounts, and stock quantities.

Product: reference data

ProductID

ProductName

Price

Discount

Stock

101

Notebook

100.00

20.00

10

102

Pen

20.00

0.00

0

Discount is a monetary amount, not a percentage.

The table follows these rules:

  • ProductID uniquely identifies each product.

  • ProductName must be present and must not be empty or contain only ordinary spaces.

  • Price, Discount, and Stock must be present and non-negative.

  • Discount cannot exceed Price.

  • An omitted Discount or Stock receives a default value of zero.

The primary key handles identification. The remaining requirements need other declarations.

Data Types: The First Boundary

A data type defines the basic kind of value a column stores.

For this example:

Column

Suitable type

Purpose

ProductID

INT

Stores an integer identifier.

ProductName

VARCHAR(80)

Stores a product name within the declared length.

Price

DECIMAL(10, 2)

Stores an exact amount with two decimal places.

Discount

DECIMAL(10, 2)

Stores an exact discount amount.

Stock

INT

Stores a whole-number quantity.

However, an integer type alone does not prevent negative stock. A decimal type alone does not prevent a discount larger than the price.

Data types and constraints therefore work together.

NOT NULL: Require a Value

NOT NULL prevents a column from containing NULL.

NULL represents missing or unknown information. It is different from zero and from an empty string.

For example, declaring ProductName as NOT NULL prevents a missing name, but it does not reject '', an empty string.

A separate check can reject empty names and names containing only ordinary spaces:

CHECK (CHAR_LENGTH(TRIM(ProductName)) > 0)

TRIM removes spaces from both ends, CHAR_LENGTH counts the remaining characters, and > means “greater than.”

This check does not claim to reject every possible whitespace character, such as tabs.

CHECK: Restrict Allowed Values

A CHECK constraint tests a condition against the row being inserted or updated.

Examples for Product include:

CHECK (Price >= 0)
CHECK (Discount >= 0)
CHECK (Stock >= 0)

Each rule rejects negative values.

Other common conditions include:

  • BETWEEN for an inclusive range.

  • IN for a permitted list of values.

  • AND when multiple conditions must hold.

  • OR when at least one condition may hold.

For example, Stock BETWEEN 0 AND 1000 would permit both endpoints, including 0 and 1000.

CHECK: Compare Columns in the Same Row

A check can also compare multiple columns:

CHECK (Discount <= Price)

<= means “less than or equal to.”

Price

Discount

Result

100.00

20.00

Accepted: the discount is below the price.

100.00

100.00

Accepted: the discount equals the price.

100.00

120.00

Rejected: the discount exceeds the price.

Checking columns together

Checking columns together

A check involving multiple columns is normally written as a table-level constraint. “Table-level” describes where it is declared, not permission to inspect every row in the table.

Why CHECK Alone Does Not Reject NULL

SQL conditions can evaluate to TRUE, FALSE, or UNKNOWN.

A CHECK constraint rejects a row when its condition is FALSE. It permits TRUE or UNKNOWN.

Consider CHECK (Price >= 0) by itself:

Price

Condition result

Outcome of this CHECK

100.00

TRUE

Passes

-10.00

FALSE

Rejected

NULL

UNKNOWN

Passes

Comparing an unknown price with zero produces UNKNOWN.

CHECK and NULL

CHECK and NULL

Therefore, a required non-negative price needs both:

Price DECIMAL(10, 2) NOT NULL CHECK (Price >= 0)

NOT NULL rejects the missing value. CHECK rejects the negative value.

The same reasoning applies to Discount <= Price: if either value can be NULL, that comparison alone may not reject the row.

DEFAULT: Supply an Omitted Value

A DEFAULT supplies a value when an insert omits a column or explicitly requests its default.

For example:

Stock INT NOT NULL DEFAULT 0

If an insert omits Stock, the database supplies zero.

However, a default is not a validation rule:

  • It does not stop an explicitly supplied negative value.

  • It does not replace NOT NULL.

  • Supplying NULL is different from omitting the column.

For this example, Stock needs a default, a NOT NULL constraint, and a non-negative check.

Three rules three jobs

Three rules three jobs

A Compact SQL Example

The examples use enforced checks in MySQL and PostgreSQL. The MySQL examples assume strict SQL mode and ordinary statements without IGNORE.

1. Create the Table with Its Rules

Objective: Define the product rules once in the schema.

Consider the following queries to create Product.

CREATE TABLE Product (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(80) NOT NULL,
Price DECIMAL(10, 2) NOT NULL,
Discount DECIMAL(10, 2) NOT NULL DEFAULT 0,
Stock INT NOT NULL DEFAULT 0,
CONSTRAINT chk_product_name
CHECK (CHAR_LENGTH(TRIM(ProductName)) > 0),
CONSTRAINT chk_product_price
CHECK (Price >= 0),
CONSTRAINT chk_product_discount
CHECK (Discount >= 0 AND Discount <= Price),
CONSTRAINT chk_product_stock
CHECK (Stock >= 0)
);

CONSTRAINT assigns a readable name to a rule. These names help identify which rule an invalid change violates.

DECIMAL(10, 2) and NUMERIC(10, 2) provide exact numeric storage with two decimal places.

Expected outcome: The empty table is created successfully.

2. Insert Valid Products and Use Defaults

Objective: Insert the reference data and let defaults supply the pen’s discount and stock.

Consider the following queries to insert and display the products.

INSERT INTO Product
(ProductID, ProductName, Price, Discount, Stock)
VALUES
(101, 'Notebook', 100.00, 20.00, 10);
INSERT INTO Product
(ProductID, ProductName, Price)
VALUES
(102, 'Pen', 20.00);
SELECT ProductID, ProductName, Price, Discount, Stock
FROM Product
ORDER BY ProductID;

Expected result

ProductID

ProductName

Price

Discount

Stock

101

Notebook

100.00

20.00

10

102

Pen

20.00

0.00

0

The second insert omits Discount and Stock, so both receive their declared defaults.

3. Reject Invalid Changes

Objective: Verify that the database protects the rules during updates.

Consider the following queries to test a missing price, an excessive discount, and negative stock.

Each statement below is a separate test. Executing them individually with autocommit enabled, outside an explicit transaction block, because each is expected to fail.

MySQL:

-- Test 1: A price is required.
UPDATE Product
SET Price = NULL
WHERE ProductID = 101;
-- Test 2: The discount cannot exceed the price.
UPDATE Product
SET Discount = 120.00
WHERE ProductID = 101;
-- Test 3: Stock cannot be negative.
UPDATE Product
SET Stock = -1
WHERE ProductID = 102;

PostgreSQL:

-- Test 1: A price is required.
UPDATE Product
SET Price = NULL
WHERE ProductID = 101;
-- Test 2: The discount cannot exceed the price.
UPDATE Product
SET Discount = 120.00
WHERE ProductID = 101;
-- Test 3: Stock cannot be negative.
UPDATE Product
SET Stock = -1
WHERE ProductID = 102;

Expected outcomes

Test

Violated rule

Outcome

Price becomes NULL

Price NOT NULL

Update rejected.

Discount becomes 120.00 while Price is 100.00

chk_product_discount

Update rejected.

Stock becomes -1

chk_product_stock

Update rejected.

These statements return constraint errors rather than result tables. Error wording differs between databases.

After the failed tests, the stored rows remain:

ProductID

ProductName

Price

Discount

Stock

101

Notebook

100.00

20.00

10

102

Pen

20.00

0.00

0

Reusable Rules with Domains

A domain is a reusable type definition that can include constraints.

For example, a non-negative monetary domain could be used for both prices and discounts. Each column would still need its own NOT NULL declaration when a value is mandatory.

PostgreSQL supports CREATE DOMAIN. MySQL does not provide the same SQL domain feature, so equivalent rules are usually declared on individual columns.

A domain can enforce “this amount cannot be negative.” Comparing a product’s discount with its price still requires a rule involving those two columns.

Rules Beyond a Single Row

An ordinary row-level CHECK is not suitable for rules such as:

The total stock across all products must not exceed 10,000.

That rule depends on several rows. MySQL and PostgreSQL do not allow a subquery inside a CHECK constraint to calculate that total.

SQL defines assertions for broader database-wide conditions, but MySQL and PostgreSQL do not implement CREATE ASSERTION.

Some databases offer specialised declarative features. For example, PostgreSQL exclusion constraints can prevent conflicting values across rows, such as overlapping reservations.

When no suitable constraint exists, a rule may need a trigger or controlled transaction logic. That logic must handle concurrent changes correctly. Checking a total first and updating later without coordination can allow two transactions to violate the rule together.

Adding Constraints to Existing Data

Constraints are easiest to define when a table is created. They can also be added later, but existing rows must be considered.

For example, adding a non-negative stock check can fail if the table already contains negative stock.

A sound approach is to:

  1. Identify existing violations.

  2. Correct them according to the business rule.

  3. Add and validate the constraint.

  4. Keep the constraint enforced for future changes.

A declared constraint should not be assumed to protect data if it has been disabled or configured as not enforced.

Summary

Declarative integrity extends beyond row identification and references. Data types define the basic value format, NOT NULL requires a value, and CHECK enforces conditions. Defaults supply omitted values but do not validate explicitly supplied data.

These features work best together. Row-level rules can compare several columns, while reusable domains and specialised constraints cover additional needs. Rules involving totals or other rows require a supported database feature or carefully coordinated transaction logic.

CS Core

Read Similar Blogs

Comments0