Constraints in SQL Server: Enforcing Reliable Data

Why Constraints Matter: Integrity Rules SQL Server Can Enforce

Constraints are integrity rules attached to a table (or a column) that SQL Server enforces automatically during INSERT, UPDATE, and sometimes DELETE. They prevent invalid data from entering your tables, which reduces downstream bugs and simplifies application logic. When a constraint is violated, SQL Server rejects the statement and returns an error describing what rule was broken.

In this chapter you will build and test constraints by intentionally attempting invalid changes, reading the error messages, and then adjusting the constraints to match real requirements.

1) NOT NULL and CHECK Constraints (Validation You Can Trust)

NOT NULL: Require a Value

NOT NULL prevents missing values. Use it when a column must always have a value (for example, a customer email or an order date). If you attempt to insert NULL, SQL Server rejects the row.

-- Clean setup for practice (run in a sandbox database/schema you control)

DROP TABLE IF EXISTS dbo.CustomerValidation;

CREATE TABLE dbo.CustomerValidation (    CustomerId int NOT NULL,    Email varchar(254) NOT NULL,    Age int NULL,    CountryCode char(2) NOT NULL);

Practice: attempt an invalid insert (missing required value).

INSERT INTO dbo.CustomerValidation (CustomerId, Email, Age, CountryCode)VALUES (1, NULL, 25, 'US');

You should see an error similar to: Cannot insert the value NULL into column 'Email'.... This is SQL Server enforcing NOT NULL.

Continue in our app.

• Listen to the audio with the screen off.
• Earn a certificate upon completion.
• Over 5000 courses for you to explore!

Or continue reading below...

Download the app

CHECK: Validate Allowed Values and Ranges

CHECK constraints enforce boolean expressions. They are ideal for range checks, allowed sets, and cross-column rules that must always be true.

Range check example: Age must be between 0 and 120

ALTER TABLE dbo.CustomerValidationADD CONSTRAINT CK_CustomerValidation_Age_RangeCHECK (Age IS NULL OR (Age BETWEEN 0 AND 120));

Practice: attempt invalid values.

INSERT INTO dbo.CustomerValidation (CustomerId, Email, Age, CountryCode)VALUES (2, 'a@b.com', -1, 'US');

INSERT INTO dbo.CustomerValidation (CustomerId, Email, Age, CountryCode)VALUES (3, 'c@d.com', 200, 'US');

Both should fail with a message referencing the CK_... constraint name. Note the pattern: allow NULL explicitly if the column is optional; otherwise the check would reject NULL too (because CHECK treats UNKNOWN as pass/fail depending on expression; writing it explicitly avoids surprises).

Allowed set example: CountryCode must be one of a known list

ALTER TABLE dbo.CustomerValidationADD CONSTRAINT CK_CustomerValidation_CountryCode_AllowedCHECK (CountryCode IN ('US','CA','GB','DE','FR'));

Practice: invalid country code.

INSERT INTO dbo.CustomerValidation (CustomerId, Email, Age, CountryCode)VALUES (4, 'e@f.com', 30, 'ZZ');

Pattern-like checks: simple email and code formats

SQL Server CHECK constraints can use LIKE for basic pattern validation. This is not a full regular expression engine, but it can catch obvious formatting issues. Keep pattern checks simple to avoid false positives/negatives.

ALTER TABLE dbo.CustomerValidationADD CONSTRAINT CK_CustomerValidation_Email_BasicFormatCHECK (Email LIKE '%_@_%._%');

Practice: invalid email formats.

INSERT INTO dbo.CustomerValidation (CustomerId, Email, Age, CountryCode)VALUES (5, 'not-an-email', 22, 'US');

INSERT INTO dbo.CustomerValidation (CustomerId, Email, Age, CountryCode)VALUES (6, 'missingdomain@', 22, 'US');

Adjusting constraints to match requirements: if your business allows internal emails without a dot (for example user@corp), the above check is too strict. You would drop and recreate it with a rule that matches your requirement.

ALTER TABLE dbo.CustomerValidationDROP CONSTRAINT CK_CustomerValidation_Email_BasicFormat;

ALTER TABLE dbo.CustomerValidationADD CONSTRAINT CK_CustomerValidation_Email_BasicFormatCHECK (Email LIKE '%_@_%');

Cross-column check: EndDate must be after StartDate

CHECK can reference multiple columns in the same row.

DROP TABLE IF EXISTS dbo.Subscription;

CREATE TABLE dbo.Subscription (    SubscriptionId int NOT NULL,    StartDate date NOT NULL,    EndDate date NULL);

ALTER TABLE dbo.SubscriptionADD CONSTRAINT CK_Subscription_EndAfterStartCHECK (EndDate IS NULL OR EndDate >= StartDate);

Practice: invalid update.

INSERT INTO dbo.Subscription (SubscriptionId, StartDate, EndDate)VALUES (1, '2026-01-10', '2026-01-05');

2) UNIQUE Constraints vs Primary Keys (and How NULLs Behave)

Both PRIMARY KEY and UNIQUE enforce uniqueness, but they communicate different intent and have different rules around NULL.

• PRIMARY KEY: identifies the row. It implies NOT NULL and uniqueness. A table can have only one primary key (which can be composite).
• UNIQUE: enforces uniqueness for a candidate key or alternate identifier. A table can have multiple unique constraints.

Primary key: uniqueness + NOT NULL

DROP TABLE IF EXISTS dbo.Product;

CREATE TABLE dbo.Product (    ProductId int NOT NULL,    Sku varchar(30) NOT NULL,    Barcode varchar(50) NULL,    ProductName varchar(200) NOT NULL,    CONSTRAINT PK_Product PRIMARY KEY (ProductId));

Practice: duplicate primary key.

INSERT INTO dbo.Product (ProductId, Sku, Barcode, ProductName)VALUES (1, 'SKU-001', NULL, 'Widget');

INSERT INTO dbo.Product (ProductId, Sku, Barcode, ProductName)VALUES (1, 'SKU-002', NULL, 'Another Widget');

Unique constraint: alternate identifiers (and multiple NULLs)

Now enforce that Sku is unique and Barcode is unique when present.

ALTER TABLE dbo.ProductADD CONSTRAINT UQ_Product_Sku UNIQUE (Sku);

ALTER TABLE dbo.ProductADD CONSTRAINT UQ_Product_Barcode UNIQUE (Barcode);

Practice: duplicate SKU.

INSERT INTO dbo.Product (ProductId, Sku, Barcode, ProductName)VALUES (2, 'SKU-001', NULL, 'Duplicate SKU');

Practice: multiple NULL barcodes. In SQL Server, a UNIQUE constraint allows multiple NULL values because NULL represents “unknown”, and SQL Server does not treat two unknowns as equal for uniqueness enforcement.

INSERT INTO dbo.Product (ProductId, Sku, Barcode, ProductName)VALUES (3, 'SKU-003', NULL, 'No Barcode A');

INSERT INTO dbo.Product (ProductId, Sku, Barcode, ProductName)VALUES (4, 'SKU-004', NULL, 'No Barcode B');

Practice: duplicate non-NULL barcode.

INSERT INTO dbo.Product (ProductId, Sku, Barcode, ProductName)VALUES (5, 'SKU-005', '12345', 'Has Barcode');

INSERT INTO dbo.Product (ProductId, Sku, Barcode, ProductName)VALUES (6, 'SKU-006', '12345', 'Duplicate Barcode');

When you need “only one NULL allowed”

If the business rule is “barcode is optional, but if it is missing it can be missing for only one product” (rare, but sometimes used for special placeholder rows), a plain UNIQUE constraint will not enforce that. One approach is a filtered unique index (not a constraint) or a different modeling choice. If you want “barcode must be unique when present” (common), the UNIQUE constraint already does that.

3) FOREIGN KEY Constraints (Parent/Child Integrity and Referential Actions)

FOREIGN KEY constraints enforce relationships between tables: a value in a child table must exist in the parent table. This prevents orphan rows and keeps relationships reliable.

Create parent and child tables

DROP TABLE IF EXISTS dbo.OrderLine;DROP TABLE IF EXISTS dbo.[Order];DROP TABLE IF EXISTS dbo.Customer;

CREATE TABLE dbo.Customer (    CustomerId int NOT NULL,    Email varchar(254) NOT NULL,    CONSTRAINT PK_Customer PRIMARY KEY (CustomerId),    CONSTRAINT UQ_Customer_Email UNIQUE (Email));

CREATE TABLE dbo.[Order] (    OrderId int NOT NULL,    CustomerId int NOT NULL,    OrderDate date NOT NULL,    CONSTRAINT PK_Order PRIMARY KEY (OrderId));

CREATE TABLE dbo.OrderLine (    OrderId int NOT NULL,    LineNo int NOT NULL,    ProductCode varchar(30) NOT NULL,    Quantity int NOT NULL,    CONSTRAINT PK_OrderLine PRIMARY KEY (OrderId, LineNo),    CONSTRAINT CK_OrderLine_Quantity_Positive CHECK (Quantity > 0));

Add foreign keys

ALTER TABLE dbo.[Order]ADD CONSTRAINT FK_Order_CustomerFOREIGN KEY (CustomerId) REFERENCES dbo.Customer(CustomerId);

ALTER TABLE dbo.OrderLineADD CONSTRAINT FK_OrderLine_OrderFOREIGN KEY (OrderId) REFERENCES dbo.[Order](OrderId);

Practice: insert child row without parent (violation)

INSERT INTO dbo.[Order] (OrderId, CustomerId, OrderDate)VALUES (100, 999, '2026-01-15');

This should fail because customer 999 does not exist.

Now insert the parent, then the child.

INSERT INTO dbo.Customer (CustomerId, Email)VALUES (999, 'parent@example.com');

INSERT INTO dbo.[Order] (OrderId, CustomerId, OrderDate)VALUES (100, 999, '2026-01-15');

INSERT INTO dbo.OrderLine (OrderId, LineNo, ProductCode, Quantity)VALUES (100, 1, 'SKU-001', 2);

Referential actions: NO ACTION vs CASCADE

Foreign keys can specify what happens when a referenced parent row is updated or deleted.

• NO ACTION (default): reject the parent change if children exist. This is safest when you want to prevent accidental mass deletes/updates.
• CASCADE: propagate the change to children automatically (delete children when parent is deleted, or update child keys when parent key changes). Use with care.

NO ACTION example (default): prevent deleting a customer with orders

Try deleting the customer that has an order. With the default behavior, SQL Server should block it.

DELETE FROM dbo.Customer WHERE CustomerId = 999;

You should see an error indicating the delete conflicted with a foreign key reference.

CASCADE example: delete order lines when an order is deleted

It is common to cascade from an order to its lines. To change referential actions, you typically drop and recreate the foreign key.

ALTER TABLE dbo.OrderLineDROP CONSTRAINT FK_OrderLine_Order;

ALTER TABLE dbo.OrderLineADD CONSTRAINT FK_OrderLine_OrderFOREIGN KEY (OrderId) REFERENCES dbo.[Order](OrderId)ON DELETE CASCADEON UPDATE NO ACTION;

Practice: delete an order and observe that its lines are removed automatically.

DELETE FROM dbo.[Order] WHERE OrderId = 100;

Verify that order lines are gone.

SELECT * FROM dbo.OrderLine WHERE OrderId = 100;

When to use CASCADE: use it when child rows have no meaning without the parent (for example, order lines without an order). When not to use it: avoid cascading deletes across large relationship graphs where a single delete could remove far more data than intended, or where you need explicit archival/audit logic.

4) Constraint Naming Conventions and Troubleshooting Violations

Naming conventions that help in real debugging

SQL Server error messages often include the constraint name. Clear names make troubleshooting faster. A common convention is:

• PK_Table for primary keys
• UQ_Table_Column (or columns) for unique constraints
• CK_Table_Rule for check constraints
• FK_ChildTable_ParentTable for foreign keys

Good names describe intent, not implementation details. For example, CK_OrderLine_Quantity_Positive is more useful than CK_OrderLine_1.

Reading and acting on common violation errors

• NOT NULL violation: you attempted to store NULL in a required column. Fix by providing a value, changing the column to allow NULL, or adding a default (if appropriate).
• CHECK violation: the value does not satisfy the rule. Fix by correcting the data, or adjusting the rule if it does not match the real requirement.
• UNIQUE/PK violation: duplicate key. Fix by choosing a different value, deduplicating existing data, or revisiting whether the column(s) should be unique.
• FOREIGN KEY violation: missing parent row or attempted parent delete/update while children exist. Fix by inserting the parent first, updating the child to a valid parent, deleting children first, or changing referential actions if the business rule supports it.

Structured practice: break it, read the message, then adjust

Scenario: you decide that Age must be required (not optional) and must be between 18 and 120. You currently allow NULL and allow 0..120.

Step 1: demonstrate current behavior (it allows NULL).

INSERT INTO dbo.CustomerValidation (CustomerId, Email, Age, CountryCode)VALUES (10, 'adult@example.com', NULL, 'US');

Step 2: adjust constraints to match the new requirement.

ALTER TABLE dbo.CustomerValidationALTER COLUMN Age int NOT NULL;

ALTER TABLE dbo.CustomerValidationDROP CONSTRAINT CK_CustomerValidation_Age_Range;

ALTER TABLE dbo.CustomerValidationADD CONSTRAINT CK_CustomerValidation_Age_AdultRangeCHECK (Age BETWEEN 18 AND 120);

Step 3: attempt invalid inserts/updates again and observe errors.

INSERT INTO dbo.CustomerValidation (CustomerId, Email, Age, CountryCode)VALUES (11, 'teen@example.com', 16, 'US');

UPDATE dbo.CustomerValidationSET Age = 10WHERE CustomerId = 10;

If the ALTER COLUMN fails because existing rows contain NULL, that is also SQL Server enforcing integrity. You must correct existing data first (for example, update NULL ages to a valid value) before tightening the rule.

Verification: List Constraints and Their Definitions from System Catalog Views

After building constraints, verify what exists in the database using system catalog views. This is essential when you inherit a database or need to confirm what rules are actually enforced.

List all constraints for a table

DECLARE @TableName sysname = 'dbo.CustomerValidation';SELECT     t.name AS table_name,    s.name AS schema_name,    kc.name AS key_constraint_name,    kc.type_desc AS key_constraint_typeFROM sys.tables tJOIN sys.schemas s ON s.schema_id = t.schema_idLEFT JOIN sys.key_constraints kc ON kc.parent_object_id = t.object_idWHERE (s.name + '.' + t.name) = @TableName;

List CHECK constraints and their definitions

SELECT     s.name AS schema_name,    t.name AS table_name,    cc.name AS check_name,    cc.definitionFROM sys.check_constraints ccJOIN sys.tables t ON t.object_id = cc.parent_object_idJOIN sys.schemas s ON s.schema_id = t.schema_idWHERE (s.name + '.' + t.name) IN ('dbo.CustomerValidation','dbo.OrderLine','dbo.Subscription')ORDER BY schema_name, table_name, check_name;

List FOREIGN KEY constraints and referential actions

SELECT     s_child.name AS child_schema,    t_child.name AS child_table,    fk.name AS foreign_key_name,    s_parent.name AS parent_schema,    t_parent.name AS parent_table,    fk.delete_referential_action_desc AS on_delete,    fk.update_referential_action_desc AS on_updateFROM sys.foreign_keys fkJOIN sys.tables t_child ON t_child.object_id = fk.parent_object_idJOIN sys.schemas s_child ON s_child.schema_id = t_child.schema_idJOIN sys.tables t_parent ON t_parent.object_id = fk.referenced_object_idJOIN sys.schemas s_parent ON s_parent.schema_id = t_parent.schema_idORDER BY child_schema, child_table, foreign_key_name;

List UNIQUE constraints and their columns

SELECT     s.name AS schema_name,    t.name AS table_name,    kc.name AS constraint_name,    kc.type_desc,    c.name AS column_name,    ic.key_ordinalFROM sys.key_constraints kcJOIN sys.tables t ON t.object_id = kc.parent_object_idJOIN sys.schemas s ON s.schema_id = t.schema_idJOIN sys.index_columns ic ON ic.object_id = t.object_id AND ic.index_id = kc.unique_index_idJOIN sys.columns c ON c.object_id = t.object_id AND c.column_id = ic.column_idWHERE kc.type IN ('PK','UQ')AND (s.name + '.' + t.name) IN ('dbo.Product','dbo.Customer')ORDER BY schema_name, table_name, constraint_name, ic.key_ordinal;