VMware ESXi and vSphere Cluster Management

SQL Constraints: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT

Learn how SQL constraints protect data integrity with NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT rules.

What SQL constraints do

A constraint is a database-enforced rule that restricts permitted values or relationships. Constraints protect data integrity, meaning the accuracy, validity, consistency, and reliability of stored data.

For example, a constraint can require every customer to have a name, prevent two products from using the same stock-keeping code, or prevent an order from referring to a customer that does not exist.

Constraints are checked during operations such as INSERT and UPDATE. If a statement would create invalid data, the database rejects it instead of storing the invalid row.

Application-side validation is still useful for user-friendly messages and early feedback. However, application validation can be bypassed by another application, an import job, an administrator, or a direct database connection. Database constraints provide the final shared enforcement layer for all clients.

Where constraints are declared

Constraints are commonly declared inside a CREATE TABLE statement. A column-level constraint appears beside one column and is convenient when the rule concerns only that column. A table-level constraint appears separately in the table definition and is especially useful for composite keys, composite uniqueness rules, and foreign keys.

CREATE TABLE products (
    product_id INTEGER CONSTRAINT pk_products PRIMARY KEY,
    product_name VARCHAR(200) CONSTRAINT nn_products_name NOT NULL,
    sku VARCHAR(40) CONSTRAINT uq_products_sku UNIQUE,
    price DECIMAL(10, 2),
    CONSTRAINT ck_products_price CHECK (price >= 0)
);

A constraint name is a schema identifier assigned to a rule. Names such as pk_products, uq_products_sku, and ck_products_price make diagnostics and later schema changes easier. Some systems generate names automatically, but generated names can be difficult to recognize.

Core SQL constraint types

NOT NULL: requiring a value

NOT NULL requires a column to contain a value rather than SQL NULL. NULL is a marker for missing, unknown, or inapplicable data; it is not an ordinary value.

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name VARCHAR(200) NOT NULL,
    signup_date DATE NOT NULL
);

In this example, a customer must have both a name and a signup date. An empty string such as '' and NULL are not necessarily equivalent. A database may accept an empty string in a text column while rejecting NULL.

There are three important insertion cases:

• Omitting a column: the database may use its default, if one exists; otherwise the result depends on whether the column allows NULL.
• Explicitly inserting NULL: this stores NULL only if the column permits it. A default generally does not replace an explicitly supplied NULL.
• Supplying a value: the supplied value is checked against all applicable constraints.

UNIQUE: preventing duplicates

UNIQUE prevents duplicate values in one column or duplicate combinations across multiple columns.

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    sku VARCHAR(40) NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    CONSTRAINT uq_products_sku UNIQUE (sku)
);

Only one product can use a particular stock-keeping code. A unique email rule works similarly:

CREATE TABLE user_accounts (
    user_id INTEGER PRIMARY KEY,
    email VARCHAR(320) NOT NULL UNIQUE
);

A multi-column unique rule applies to the combination, not to each column separately. For example, a student may enroll in many courses, and a course may have many students, but the same student-course pair should occur only once.

CREATE TABLE enrollments (
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    enrolled_on DATE NOT NULL,
    CONSTRAINT uq_enrollment_student_course UNIQUE (student_id, course_id)
);

Database systems commonly implement unique rules with a unique index or an index-like structure. The constraint expresses the integrity rule; the index also helps the database find duplicates efficiently. The exact implementation is product-specific.

Multiple NULL values under a UNIQUE rule can be accepted, rejected, or treated differently depending on the SQL product and its configuration. If every row must have a distinct real value, combine UNIQUE with NOT NULL.

PRIMARY KEY: identifying rows

A primary key is the designated identifier for rows in a table. Its values must be unique and cannot be NULL. A table has one primary-key definition, although that definition may contain one column or several columns.

Single-column primary key

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name VARCHAR(200) NOT NULL
);

customer_id is a surrogate numeric identifier: it has no business meaning and exists primarily to identify the row. Depending on the database system, you may use an identity, generated, sequence-backed, or auto-incrementing column instead of manually supplying the number.

Composite primary key

A composite key is made from two or more columns. It is useful when the combination identifies a row naturally.

CREATE TABLE course_enrollments (
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    enrolled_on DATE NOT NULL,
    CONSTRAINT pk_course_enrollments PRIMARY KEY (student_id, course_id)
);

The pair (student_id, course_id) must be unique, and neither part can be NULL. In practice, the two columns would usually also be foreign keys to student and course tables.

FOREIGN KEY and referential integrity

A foreign key is a column or column set whose values refer to a primary key or suitable unique candidate key in another table. The referenced table is the parent table; the table containing the foreign key is the child table. The resulting rule is called referential integrity: related records must point to valid referenced records.

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name VARCHAR(200) NOT NULL
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date DATE NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers (customer_id)
        ON DELETE RESTRICT
        ON UPDATE NO ACTION
);

Before an order with customer_id = 42 can be stored, customer 42 must exist. Because the child column is NOT NULL, every order must have a customer. If the foreign-key column allowed NULL, a NULL child value could represent an order with no associated customer, subject to the database's foreign-key rules.

Actions when a parent changes

Cascading actions require careful design. A delete cascade can remove many rows, and an update cascade can change identifiers throughout dependent tables. Choose behavior based on the meaning and retention requirements of the data rather than convenience.

CHECK: enforcing conditions

CHECK requires a Boolean condition to be acceptable before a row is stored. Typical rules include nonnegative quantities, positive prices, rating ranges, approved status values, and valid date relationships.

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 0,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    CONSTRAINT ck_products_price CHECK (price >= 0),
    CONSTRAINT ck_products_quantity CHECK (quantity >= 0),
    CONSTRAINT ck_products_status CHECK (status IN ('active', 'discontinued'))
);

A table-level check can compare columns in the same row:

CREATE TABLE promotions (
    promotion_id INTEGER PRIMARY KEY,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    CONSTRAINT ck_promotions_dates CHECK (end_date >= start_date)
);

SQL uses three-valued logic: a condition can evaluate to true, false, or unknown. In many systems, a CHECK condition that evaluates to unknown because of NULL is not rejected. Therefore, use NOT NULL when a value is mandatory, and write the CHECK condition to reflect the intended treatment of missing values.

Supported expressions and historical enforcement behavior differ among database products. Test important rules on the target system, especially when expressions involve functions, other rows, or database-specific features.

DEFAULT: supplying omitted values

A DEFAULT supplies a value when an INSERT omits the column.

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    quantity INTEGER NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

If an INSERT omits status, the database can store 'pending'. If it omits quantity, it can store 1. A default does not normally override an explicitly supplied value.

INSERT INTO orders (order_id) VALUES (1001);
-- Uses defaults for status, quantity, and created_at.

INSERT INTO orders (order_id, status) VALUES (1002, 'shipped');
-- Uses the explicit status and defaults for the other omitted columns.

Explicitly inserting NULL is different from omitting the column. If the column is NOT NULL, an explicit NULL usually fails even when a default exists. If the column permits NULL, the result is generally NULL rather than the default. Verify behavior for the target SQL product and insertion syntax.

Complete table example

The following product catalog combines a primary key, required fields, a unique business code, check conditions, defaults, and a foreign key to a category table.

CREATE TABLE categories (
    category_id INTEGER PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE products (
    product_id INTEGER CONSTRAINT pk_products PRIMARY KEY,
    category_id INTEGER NOT NULL,
    sku VARCHAR(40) NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 0,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    CONSTRAINT uq_products_sku UNIQUE (sku),
    CONSTRAINT ck_products_price CHECK (price >= 0),
    CONSTRAINT ck_products_quantity CHECK (quantity >= 0),
    CONSTRAINT ck_products_status CHECK (status IN ('active', 'discontinued')),
    CONSTRAINT fk_products_category
        FOREIGN KEY (category_id)
        REFERENCES categories (category_id)
        ON DELETE RESTRICT
        ON UPDATE NO ACTION
);

The general structure is CREATE TABLE table_name (column_name data_type [column constraints], ..., [table constraints]);. Put each column on its own line, use meaningful names, and group table-level constraints after the column definitions. Declaration order usually does not change the logical meaning, but readable formatting makes review and troubleshooting easier.

Data type names are not identical across all SQL platforms. For example, integer, variable-length character, decimal, date, and timestamp types may have different names, limits, or generated-value syntax. Adapt the data types and current-timestamp expression to your database.

Course enrollment example

An enrollment table can use a composite primary key and two foreign keys:

CREATE TABLE enrollments (
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    enrolled_on DATE NOT NULL DEFAULT CURRENT_DATE,
    CONSTRAINT pk_enrollments PRIMARY KEY (student_id, course_id),
    CONSTRAINT fk_enrollments_student
        FOREIGN KEY (student_id) REFERENCES students (student_id),
    CONSTRAINT fk_enrollments_course
        FOREIGN KEY (course_id) REFERENCES courses (course_id)
);

The composite primary key prevents duplicate enrollment in the same course. The two foreign keys ensure that both the student and course exist before the enrollment is stored.

Constraint violations during INSERT and UPDATE

Constraints apply to both new rows and changed rows. Representative failures include:

• Inserting an order without a required order date violates NOT NULL.
• Inserting a product with an existing SKU violates UNIQUE.
• Changing a product's identifier to one already used by another row violates a primary key or unique rule.
• Inserting an order with a nonexistent customer identifier violates a FOREIGN KEY.
• Changing a quantity to -1 violates a quantity CHECK.
• Changing an end date to a date earlier than its start date violates a multi-column CHECK.

INSERT INTO orders (order_id, customer_id, order_date)
VALUES (2001, 999999, CURRENT_DATE);
-- Fails if customer 999999 does not exist.

UPDATE products
SET price = -5
WHERE product_id = 10;
-- Fails when the price CHECK requires price >= 0.

Correct the source data, reference the correct existing key, or update the intended existing row. If a statement fails repeatedly, inspect the constraint name and compare the rule with the actual business requirement. Do not remove a legitimate constraint merely to make an invalid write succeed. If the schema rule is genuinely wrong, revise it deliberately after assessing existing data and dependent applications.

Managing constraints after table creation

ALTER TABLE is the typical mechanism for adding, dropping, or changing constraints after a table exists.

ALTER TABLE products
ADD CONSTRAINT ck_products_name_length
CHECK (CHAR_LENGTH(product_name) > 0);

Removal and alteration syntax differs substantially between database products. A typical pattern is:

ALTER TABLE products DROP CONSTRAINT ck_products_name_length;

Some systems use different syntax for dropping unique constraints, primary keys, indexes, or foreign keys. Named constraints make the intended object easier to identify and remove reliably.

When adding a constraint to an existing table, current rows must satisfy it. The operation can fail because of duplicate values, unexpected NULLs, invalid ranges, or orphaned foreign-key values. Before adding a rule, locate and repair violating rows, decide how exceptional data should be handled, and then apply the constraint.

DROP TABLE removes a table, but foreign-key dependencies affect the order. Drop or alter child tables and their foreign keys before parent tables, or use a database-supported cascade only when the resulting deletions are fully understood.

DROP TABLE enrollments;
DROP TABLE students;

The exact dependency and cascade behavior is database-specific. Treat table removal as a schema change that should be reviewed and tested.

Troubleshooting checklist

• Required-column failure: provide a valid non-NULL value, define an appropriate default for omitted values, or make the field optional only when the business rule permits it.
• Duplicate email or code: use a distinct value, update the intended existing row, or verify that the uniqueness rule matches the real requirement.
• Missing parent row: create or identify the parent first, use the correct key, or allow NULL only when the relationship is legitimately optional.
• Parent deletion blocked: delete or reassign dependent rows, or select a deliberate cascade or SET NULL policy if appropriate.
• Quantity, price, date, or status rejected: inspect the named CHECK constraint and supply values satisfying its condition.
• Constraint addition fails: find and repair existing duplicate, NULL, invalid-range, or orphaned rows before adding the rule.
• Default not used: check whether the column was omitted or explicitly assigned NULL, then verify the target database's default behavior.

Summary

Use constraints to express data rules where the data is stored: NOT NULL for required values, UNIQUE for duplicate prevention, PRIMARY KEY for row identity, FOREIGN KEY for valid relationships, CHECK for allowed conditions, and DEFAULT for omitted values. Declare them clearly in CREATE TABLE, name important rules, test both INSERT and UPDATE paths, and manage later changes with care because existing data and foreign-key dependencies matter.

Continue with SQL constraints when reviewing these rules and their table-design patterns.