Primary keys, foreign keys, and database constraints

Overview

Primary keys, foreign keys, and constraints are core relational database concepts used to protect data integrity. They define what valid data looks like, how rows are uniquely identified, and how tables relate to each other. In SQL Server and most relational databases, constraints are not just documentation. They are rules enforced by the database engine.

A primary key identifies each row in a table. A foreign key connects rows in one table to rows in another table. Other constraints, such as NOT NULL, UNIQUE, CHECK, and DEFAULT, control whether values are required, unique, valid, or automatically populated.

This topic matters because application code alone is not enough to protect data. If multiple applications, APIs, jobs, scripts, imports, and users can write to the same database, the database must enforce the most important rules. Constraints prevent invalid rows, orphan records, duplicate business keys, missing required values, and inconsistent relationships.

For interviews, this topic is important because it tests both SQL fundamentals and real-world design judgment. A strong candidate should be able to explain:

• What primary keys and foreign keys are.
• How constraints enforce entity integrity, referential integrity, and domain integrity.
• The difference between primary key, foreign key, unique constraint, check constraint, default constraint, and not-null constraint.
• How constraints affect inserts, updates, deletes, joins, indexing, and query plans.
• Why a foreign key column should often be indexed.
• How cascade delete works and when it is risky.
• How to choose between natural keys, surrogate keys, and composite keys.
• How database constraints relate to application validation.

In practical systems, constraints are part of the contract of the database. They make data safer, queries more reliable, and bugs easier to detect early.

Core Concepts

What is a database constraint?

A constraint is a rule defined on a table or column that limits what data can be stored. Constraints help the database enforce correctness.

Common SQL constraints include:

Constraints are useful because they keep invalid data out of the database even when bugs exist in application code.

Example:

CREATE TABLE Customers
(
    CustomerId INT IDENTITY(1,1) NOT NULL,
    Email NVARCHAR(320) NOT NULL,
    FullName NVARCHAR(200) NOT NULL,
    Status NVARCHAR(20) NOT NULL,
    CreatedAtUtc DATETIME2 NOT NULL
        CONSTRAINT DF_Customers_CreatedAtUtc DEFAULT SYSUTCDATETIME(),

    CONSTRAINT PK_Customers PRIMARY KEY (CustomerId),
    CONSTRAINT UQ_Customers_Email UNIQUE (Email),
    CONSTRAINT CK_Customers_Status CHECK (Status IN ('Active', 'Inactive', 'Blocked'))
);

This table enforces:

• Every customer has an ID.
• Every customer has an email.
• Emails are unique.
• Status must be one of a known set of values.
• Creation time defaults to the current UTC time.

Why constraints matter

Constraints protect data integrity at the database level. This matters because data can be written from many places:

• Web APIs.
• Admin tools.
• Background jobs.
• ETL imports.
• Stored procedures.
• Data repair scripts.
• Integration services.
• Direct database access.
• Multiple application versions during deployment.

If validation exists only in application code, another writer can bypass it. Database constraints enforce critical rules consistently.

Example problem without a foreign key:

Orders table contains CustomerId = 999
Customers table has no CustomerId = 999

This is called an orphan row. It makes joins unreliable and can break reports, APIs, and business workflows.

With a foreign key, the database rejects the invalid order.

Entity integrity, referential integrity, and domain integrity

Interviewers often expect these terms.

Entity integrity

Entity integrity means each row in a table can be uniquely identified. Primary keys enforce entity integrity.

Example:

CONSTRAINT PK_Orders PRIMARY KEY (OrderId)

This prevents duplicate OrderId values and prevents OrderId from being NULL.

Referential integrity

Referential integrity means relationships between tables remain valid. Foreign keys enforce referential integrity.

Example:

CONSTRAINT FK_Orders_Customers
FOREIGN KEY (CustomerId)
REFERENCES Customers(CustomerId)

This prevents an order from referencing a customer that does not exist.

Domain integrity

Domain integrity means values in a column are valid for that column's business domain. CHECK, NOT NULL, DEFAULT, and data types help enforce domain integrity.

Example:

CONSTRAINT CK_Products_Price
CHECK (Price >= 0)

This prevents negative product prices.

Primary keys

A primary key is a column or set of columns that uniquely identifies each row in a table.

Example:

CREATE TABLE Products
(
    ProductId INT IDENTITY(1,1) NOT NULL,
    Name NVARCHAR(200) NOT NULL,
    Price DECIMAL(18, 2) NOT NULL,

    CONSTRAINT PK_Products PRIMARY KEY (ProductId)
);

A primary key has two important properties:

• It must be unique.
• It cannot contain NULL.

A table can have only one primary key constraint, but that primary key can be made of one column or multiple columns.

Primary key and unique index behavior

In SQL Server, a primary key constraint is enforced using a unique index. By default, SQL Server may create it as a clustered index unless specified otherwise and unless a clustered index already exists. You can explicitly choose clustered or nonclustered.

Example:

CREATE TABLE Orders
(
    OrderId BIGINT NOT NULL,
    CustomerId INT NOT NULL,
    CreatedAtUtc DATETIME2 NOT NULL,

    CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (OrderId)
);

Or:

CREATE TABLE Orders
(
    OrderId BIGINT NOT NULL,
    CustomerId INT NOT NULL,
    CreatedAtUtc DATETIME2 NOT NULL,

    CONSTRAINT PK_Orders PRIMARY KEY NONCLUSTERED (OrderId)
);

Important interview point:

A primary key is a logical constraint.
A clustered index is a physical storage/indexing choice.
They are related in SQL Server, but they are not the same concept.

Surrogate keys vs natural keys

A primary key can be a surrogate key or a natural key.

Surrogate key

A surrogate key is an artificial identifier created by the system.

Examples:

CustomerId INT IDENTITY(1,1)
OrderId UNIQUEIDENTIFIER
ProductId BIGINT

Benefits:

• Usually small and stable.
• Does not change when business attributes change.
• Good for joins and relationships.
• Hides business meaning.
• Easy to reference from child tables.

Trade-offs:

• Does not prevent duplicate real-world business values by itself.
• Often needs additional UNIQUE constraints.

Example:

CREATE TABLE Customers
(
    CustomerId INT IDENTITY(1,1) NOT NULL,
    Email NVARCHAR(320) NOT NULL,

    CONSTRAINT PK_Customers PRIMARY KEY (CustomerId),
    CONSTRAINT UQ_Customers_Email UNIQUE (Email)
);

Here CustomerId is the surrogate primary key, while Email is a business key protected by a unique constraint.

Natural key

A natural key is a real business value that uniquely identifies a row.

Examples:

Email
NationalId
CountryCode
SKU
ISBN

Benefits:

• Meaningful to the business.
• Can prevent duplicate business records directly.
• Sometimes avoids an extra column.

Trade-offs:

• Business values can change.
• Values may be long or composite.
• Privacy concerns may exist.
• Business uniqueness rules can evolve.
• Foreign keys become larger if the natural key is referenced by many tables.

Practical recommendation:

Use surrogate keys for stable internal identity.
Use unique constraints for important natural/business keys.

Composite primary keys

A composite primary key uses multiple columns to uniquely identify a row.

Example many-to-many join table:

CREATE TABLE StudentCourses
(
    StudentId INT NOT NULL,
    CourseId INT NOT NULL,
    EnrolledAtUtc DATETIME2 NOT NULL
        CONSTRAINT DF_StudentCourses_EnrolledAtUtc DEFAULT SYSUTCDATETIME(),

    CONSTRAINT PK_StudentCourses PRIMARY KEY (StudentId, CourseId)
);

This means a student can enroll in a course only once.

Composite keys are common in:

• Join tables.
• Associative entities.
• Multi-tenant tables.
• Historical/versioned tables.
• Tables where identity is naturally made from multiple attributes.

Example multi-tenant key:

CREATE TABLE TenantUsers
(
    TenantId INT NOT NULL,
    UserId INT NOT NULL,
    DisplayName NVARCHAR(200) NOT NULL,

    CONSTRAINT PK_TenantUsers PRIMARY KEY (TenantId, UserId)
);

Composite keys can be useful but may make foreign keys and joins more verbose.

Foreign keys

A foreign key is a column or group of columns in one table that references a candidate key in another table, usually the primary key.

Example:

CREATE TABLE Orders
(
    OrderId INT IDENTITY(1,1) NOT NULL,
    CustomerId INT NOT NULL,
    OrderDateUtc DATETIME2 NOT NULL,

    CONSTRAINT PK_Orders PRIMARY KEY (OrderId),
    CONSTRAINT FK_Orders_Customers
        FOREIGN KEY (CustomerId)
        REFERENCES Customers(CustomerId)
);

Terminology:

In this example:

• Customers is the parent table.
• Orders is the child table.
• Customers.CustomerId is the referenced key.
• Orders.CustomerId is the foreign key column.

What a foreign key prevents

A foreign key prevents invalid relationships.

Example:

INSERT INTO Orders (CustomerId, OrderDateUtc)
VALUES (999, SYSUTCDATETIME());

If no customer with CustomerId = 999 exists, the database rejects the insert.

A foreign key also affects parent row updates and deletes. If an order references a customer, the database prevents deleting that customer unless a referential action such as cascade delete is configured or the child rows are handled first.

Nullable foreign keys

A foreign key column can be nullable unless you explicitly define it as NOT NULL.

Example:

CREATE TABLE Orders
(
    OrderId INT IDENTITY(1,1) NOT NULL,
    SalesRepId INT NULL,

    CONSTRAINT PK_Orders PRIMARY KEY (OrderId),
    CONSTRAINT FK_Orders_SalesReps
        FOREIGN KEY (SalesRepId)
        REFERENCES SalesReps(SalesRepId)
);

If SalesRepId is NULL, the row does not need to match a parent row. If it has a non-null value, it must reference a valid SalesRepId.

Design meaning:

• NULL foreign key means the relationship is optional.
• NOT NULL foreign key means the relationship is required.

Example:

CustomerId INT NOT NULL -- every order must have a customer
SalesRepId INT NULL     -- an order may or may not have a sales rep

Foreign key actions on delete and update

Foreign keys can define what happens when the referenced parent row is deleted or updated.

Common actions:

Example cascade delete:

CREATE TABLE OrderItems
(
    OrderItemId INT IDENTITY(1,1) NOT NULL,
    OrderId INT NOT NULL,
    ProductId INT NOT NULL,
    Quantity INT NOT NULL,

    CONSTRAINT PK_OrderItems PRIMARY KEY (OrderItemId),
    CONSTRAINT FK_OrderItems_Orders
        FOREIGN KEY (OrderId)
        REFERENCES Orders(OrderId)
        ON DELETE CASCADE
);

If an order is deleted, its order items are deleted automatically.

Cascade delete trade-offs

Cascade delete can be useful for dependent child rows that have no meaning without the parent.

Good candidates:

• Order items when an unsaved draft order is deleted.
• Temporary child records.
• Join table rows.
• Owned/dependent records.

Risky candidates:

• Financial transactions.
• Audit logs.
• Historical records.
• Records with legal or compliance requirements.
• Shared child records referenced by multiple workflows.
• Tables with deep cascade chains.

A safer alternative is often soft delete, explicit delete workflow, or restricted delete.

Example restricted delete:

CONSTRAINT FK_Orders_Customers
    FOREIGN KEY (CustomerId)
    REFERENCES Customers(CustomerId)
    ON DELETE NO ACTION

This forces the application or database procedure to decide what should happen to child records.

Unique constraints

A unique constraint ensures that values in one or more columns are unique across the table.

Example:

CREATE TABLE Users
(
    UserId INT IDENTITY(1,1) NOT NULL,
    Email NVARCHAR(320) NOT NULL,
    UserName NVARCHAR(100) NOT NULL,

    CONSTRAINT PK_Users PRIMARY KEY (UserId),
    CONSTRAINT UQ_Users_Email UNIQUE (Email),
    CONSTRAINT UQ_Users_UserName UNIQUE (UserName)
);

A table can have multiple unique constraints.

Primary key vs unique constraint:

In SQL Server, a unique constraint creates a unique index. Unique constraints are often used for business rules such as unique email, username, SKU, or external reference number.

Unique constraints and NULL

Handling of NULL in unique constraints depends on the database system. In SQL Server, a unique constraint generally allows only one NULL value for a single nullable column.

Example:

CREATE TABLE Employees
(
    EmployeeId INT IDENTITY(1,1) NOT NULL,
    BadgeNumber NVARCHAR(50) NULL,

    CONSTRAINT PK_Employees PRIMARY KEY (EmployeeId),
    CONSTRAINT UQ_Employees_BadgeNumber UNIQUE (BadgeNumber)
);

If multiple employees can have no badge number, this unique constraint may not match the intended business rule in SQL Server.

A filtered unique index can be a better SQL Server approach:

CREATE UNIQUE INDEX UX_Employees_BadgeNumber_NotNull
ON Employees(BadgeNumber)
WHERE BadgeNumber IS NOT NULL;

This enforces uniqueness only when BadgeNumber is not null.

Check constraints

A check constraint enforces a Boolean condition on column values.

Example:

CREATE TABLE Products
(
    ProductId INT IDENTITY(1,1) NOT NULL,
    Name NVARCHAR(200) NOT NULL,
    Price DECIMAL(18, 2) NOT NULL,
    StockQuantity INT NOT NULL,

    CONSTRAINT PK_Products PRIMARY KEY (ProductId),
    CONSTRAINT CK_Products_Price CHECK (Price >= 0),
    CONSTRAINT CK_Products_StockQuantity CHECK (StockQuantity >= 0)
);

Check constraints are useful for domain rules:

CHECK (Age >= 0)
CHECK (Price >= 0)
CHECK (Status IN ('Pending', 'Paid', 'Cancelled'))
CHECK (StartDate <= EndDate)
CHECK (DiscountPercent BETWEEN 0 AND 100)

Important detail:

A CHECK constraint rejects FALSE.
If the expression evaluates to UNKNOWN because of NULL, it may pass.
Use NOT NULL when the value is required.

Example:

CREATE TABLE Products
(
    ProductId INT NOT NULL,
    Price DECIMAL(18, 2) NULL,

    CONSTRAINT CK_Products_Price CHECK (Price >= 0)
);

If Price is NULL, Price >= 0 is unknown, not false. If price is required, also define Price NOT NULL.

NOT NULL constraints

NOT NULL requires a column to have a value.

Example:

CREATE TABLE Customers
(
    CustomerId INT IDENTITY(1,1) NOT NULL,
    Email NVARCHAR(320) NOT NULL,
    FullName NVARCHAR(200) NOT NULL,

    CONSTRAINT PK_Customers PRIMARY KEY (CustomerId)
);

Use NOT NULL when a value is required for the row to be meaningful.

Common examples:

• Email for a user account.
• OrderDateUtc for an order.
• Status for a workflow record.
• CreatedAtUtc for auditability.
• CustomerId for a required relationship.

Avoid making everything nullable by default. Nullable columns should represent a real optional value or unknown state.

DEFAULT constraints

A default constraint provides a value when an insert does not supply one.

Example:

CREATE TABLE Orders
(
    OrderId INT IDENTITY(1,1) NOT NULL,
    Status NVARCHAR(20) NOT NULL
        CONSTRAINT DF_Orders_Status DEFAULT 'Pending',
    CreatedAtUtc DATETIME2 NOT NULL
        CONSTRAINT DF_Orders_CreatedAtUtc DEFAULT SYSUTCDATETIME(),

    CONSTRAINT PK_Orders PRIMARY KEY (OrderId)
);

Insert:

INSERT INTO Orders DEFAULT VALUES;

The database supplies:

Status = 'Pending'
CreatedAtUtc = current UTC date/time

Defaults are useful for:

• Created timestamps.
• Initial status.
• Boolean flags.
• Default quantity.
• Tenant or system-generated values in controlled scenarios.

Important:

A DEFAULT is used only when the column is omitted or DEFAULT is explicitly requested.
It does not override an explicit NULL unless the column is NOT NULL and NULL is rejected.

Composite foreign keys

A foreign key can reference a composite primary key or unique key.

Example:

CREATE TABLE TenantUsers
(
    TenantId INT NOT NULL,
    UserId INT NOT NULL,
    DisplayName NVARCHAR(200) NOT NULL,

    CONSTRAINT PK_TenantUsers PRIMARY KEY (TenantId, UserId)
);

CREATE TABLE TenantUserSessions
(
    TenantId INT NOT NULL,
    UserId INT NOT NULL,
    SessionId UNIQUEIDENTIFIER NOT NULL,

    CONSTRAINT PK_TenantUserSessions PRIMARY KEY (SessionId),
    CONSTRAINT FK_TenantUserSessions_TenantUsers
        FOREIGN KEY (TenantId, UserId)
        REFERENCES TenantUsers(TenantId, UserId)
);

Composite foreign keys are useful when the parent identity is composite. They are common in multi-tenant schemas and join tables.

Many-to-many relationships

Many-to-many relationships are usually modeled with a join table.

Example:

CREATE TABLE Students
(
    StudentId INT IDENTITY(1,1) NOT NULL,
    FullName NVARCHAR(200) NOT NULL,
    CONSTRAINT PK_Students PRIMARY KEY (StudentId)
);

CREATE TABLE Courses
(
    CourseId INT IDENTITY(1,1) NOT NULL,
    Name NVARCHAR(200) NOT NULL,
    CONSTRAINT PK_Courses PRIMARY KEY (CourseId)
);

CREATE TABLE StudentCourses
(
    StudentId INT NOT NULL,
    CourseId INT NOT NULL,
    EnrolledAtUtc DATETIME2 NOT NULL
        CONSTRAINT DF_StudentCourses_EnrolledAtUtc DEFAULT SYSUTCDATETIME(),

    CONSTRAINT PK_StudentCourses PRIMARY KEY (StudentId, CourseId),
    CONSTRAINT FK_StudentCourses_Students
        FOREIGN KEY (StudentId)
        REFERENCES Students(StudentId),
    CONSTRAINT FK_StudentCourses_Courses
        FOREIGN KEY (CourseId)
        REFERENCES Courses(CourseId)
);

The composite primary key prevents duplicate enrollment for the same student and course.

One-to-one relationships

One-to-one relationships can be modeled by making a foreign key unique.

Example:

CREATE TABLE Users
(
    UserId INT IDENTITY(1,1) NOT NULL,
    Email NVARCHAR(320) NOT NULL,

    CONSTRAINT PK_Users PRIMARY KEY (UserId),
    CONSTRAINT UQ_Users_Email UNIQUE (Email)
);

CREATE TABLE UserProfiles
(
    UserProfileId INT IDENTITY(1,1) NOT NULL,
    UserId INT NOT NULL,
    DisplayName NVARCHAR(200) NOT NULL,

    CONSTRAINT PK_UserProfiles PRIMARY KEY (UserProfileId),
    CONSTRAINT UQ_UserProfiles_UserId UNIQUE (UserId),
    CONSTRAINT FK_UserProfiles_Users
        FOREIGN KEY (UserId)
        REFERENCES Users(UserId)
);

The foreign key ensures the profile belongs to a real user. The unique constraint ensures one user has at most one profile.

Another pattern is using the same key as both primary key and foreign key:

CREATE TABLE UserProfiles
(
    UserId INT NOT NULL,
    DisplayName NVARCHAR(200) NOT NULL,

    CONSTRAINT PK_UserProfiles PRIMARY KEY (UserId),
    CONSTRAINT FK_UserProfiles_Users
        FOREIGN KEY (UserId)
        REFERENCES Users(UserId)
);

This is common when the child row is an extension of the parent row.

Constraints and joins

Primary keys and foreign keys make joins meaningful and reliable.

Example:

SELECT
    o.OrderId,
    c.Email,
    o.OrderDateUtc
FROM Orders AS o
INNER JOIN Customers AS c
    ON c.CustomerId = o.CustomerId;

The foreign key tells the database that each Orders.CustomerId must reference a valid customer. This improves data correctness and can help the optimizer reason about relationships.

Without a foreign key, an inner join might silently drop orphan orders:

Order exists, but customer does not exist.
INNER JOIN removes that order from the result.

With a foreign key, that invalid state is prevented.

Indexing foreign keys

In SQL Server, creating a foreign key does not automatically create an index on the foreign key column. However, indexing foreign keys is often important.

Example:

CREATE INDEX IX_Orders_CustomerId
ON Orders(CustomerId);

This can help with:

• Joins from child to parent.
• Finding all orders for a customer.
• Deletes or updates on the parent table.
• Referential integrity checks.
• Reducing locking and blocking in some workloads.

Example query:

SELECT *
FROM Orders
WHERE CustomerId = @CustomerId;

Without an index on CustomerId, SQL Server may need to scan the Orders table.

Not every foreign key needs a separate index, especially on small tables or rarely queried relationships, but foreign key indexing should be reviewed during schema design.

Constraint naming

Good constraint names make errors, migrations, and database maintenance easier.

Recommended naming style:

PK_<TableName>
FK_<ChildTable>_<ParentTable>
UQ_<TableName>_<ColumnName>
CK_<TableName>_<RuleName>
DF_<TableName>_<ColumnName>

Example:

CONSTRAINT PK_Orders PRIMARY KEY (OrderId),
CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId),
CONSTRAINT CK_Orders_Total CHECK (TotalAmount >= 0),
CONSTRAINT DF_Orders_Status DEFAULT 'Pending' FOR Status

Avoid relying on system-generated names such as:

DF__Orders__Status__5AEE82B9

Generated names make migration scripts and troubleshooting harder.

Adding constraints to existing tables

Constraints can be added after table creation.

Example:

ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Customers
FOREIGN KEY (CustomerId)
REFERENCES Customers(CustomerId);

If existing data violates the new constraint, the statement fails. Before adding constraints to existing tables, you may need to clean bad data.

Find orphan orders:

SELECT o.*
FROM Orders AS o
LEFT JOIN Customers AS c
    ON c.CustomerId = o.CustomerId
WHERE c.CustomerId IS NULL;

After fixing invalid rows, add the constraint.

Disabling or not trusting constraints

In SQL Server, constraints can be disabled or created in ways that existing data is not fully checked. This can lead to untrusted constraints.

A trusted constraint means SQL Server knows all existing data satisfies it. A not-trusted constraint may still check future changes but cannot be fully relied on by the optimizer.

For interview purposes, the main point is:

Do not disable constraints casually.
If constraints were disabled during bulk loads, re-enable and validate them.

Bulk imports sometimes disable constraints for performance, but the data must be validated afterward.

Constraints vs application validation

Application validation and database constraints serve different purposes.

Example:

Application validation:

if (request.Price < 0)
{
    return BadRequest("Price must be greater than or equal to zero.");
}

Database constraint:

CONSTRAINT CK_Products_Price CHECK (Price >= 0)

Use both for important rules. Application validation improves user experience. Database constraints guarantee integrity.

Constraints and transactions

Constraints are checked as part of data modification statements and transactions. If a constraint violation occurs, the statement fails. Depending on transaction handling, the transaction may be rolled back.

Example:

BEGIN TRANSACTION;

INSERT INTO Orders (CustomerId, OrderDateUtc)
VALUES (999, SYSUTCDATETIME());

COMMIT TRANSACTION;

If CustomerId = 999 does not exist and a foreign key is defined, the insert fails. The transaction should be handled appropriately.

In application code, constraint violations often surface as database exceptions. A production application should translate important known constraint errors into meaningful API responses where appropriate.

Constraints and normalization

Constraints support normalized relational design.

Examples:

• Primary keys identify entities.
• Foreign keys represent relationships between entities.
• Unique constraints enforce candidate keys.
• Check constraints enforce valid domains.
• Not-null constraints enforce required attributes.

For example, instead of storing repeated customer information on every order, a normalized design uses a Customers table and an Orders table connected by a foreign key.

Customers(CustomerId, Email, FullName)
Orders(OrderId, CustomerId, OrderDateUtc)

This reduces duplication and improves consistency.

Constraints and soft deletes

Soft delete means marking a row as deleted instead of physically deleting it.

Example:

ALTER TABLE Customers
ADD IsDeleted BIT NOT NULL
    CONSTRAINT DF_Customers_IsDeleted DEFAULT 0;

Soft delete complicates uniqueness and foreign keys.

Example problem:

A customer with Email = [email protected] is soft deleted.
Can a new customer reuse the same email?

If yes, a normal unique constraint on Email may be too strict. A filtered unique index may be needed:

CREATE UNIQUE INDEX UX_Customers_Email_Active
ON Customers(Email)
WHERE IsDeleted = 0;

Foreign keys still reference physical rows, even if the parent row is soft deleted. Application logic must decide whether child rows can reference soft-deleted parents.

Constraints and multi-tenancy

In multi-tenant systems, constraints often need to include TenantId.

Example:

CREATE TABLE Customers
(
    TenantId INT NOT NULL,
    CustomerId INT NOT NULL,
    Email NVARCHAR(320) NOT NULL,

    CONSTRAINT PK_Customers PRIMARY KEY (TenantId, CustomerId),
    CONSTRAINT UQ_Customers_Tenant_Email UNIQUE (TenantId, Email)
);

This allows the same email to exist in different tenants while enforcing uniqueness within each tenant.

Foreign keys should also include TenantId to prevent cross-tenant references:

CREATE TABLE Orders
(
    TenantId INT NOT NULL,
    OrderId INT NOT NULL,
    CustomerId INT NOT NULL,

    CONSTRAINT PK_Orders PRIMARY KEY (TenantId, OrderId),
    CONSTRAINT FK_Orders_Customers
        FOREIGN KEY (TenantId, CustomerId)
        REFERENCES Customers(TenantId, CustomerId)
);

This prevents an order in tenant A from referencing a customer in tenant B.

Constraint errors in applications

Constraint violations are common in real applications. Examples:

• Duplicate email violates a unique constraint.
• Missing required value violates NOT NULL.
• Invalid status violates CHECK.
• Invalid parent ID violates FOREIGN KEY.
• Deleting a parent row violates a child foreign key.

A good application should handle expected constraint errors gracefully.

Example API behavior:

The database should protect the data, while the API should provide clear error messages.

Common mistakes

Common mistakes include:

• Creating tables without primary keys.
• Using natural keys as primary keys when they can change.
• Using surrogate keys but forgetting unique constraints on business keys.
• Not indexing foreign key columns.
• Confusing primary keys with clustered indexes.
• Assuming a foreign key automatically creates an index.
• Using cascade delete on important historical data.
• Making optional relationships required by mistake.
• Making required relationships nullable by mistake.
• Forgetting that CHECK constraints may allow NULL unless combined with NOT NULL.
• Relying only on application validation.
• Using inconsistent constraint names.
• Disabling constraints during bulk load and not validating afterward.
• Ignoring multi-tenant uniqueness and cross-tenant reference rules.
• Using composite keys everywhere without considering complexity.
• Handling database constraint exceptions as generic 500 Internal Server Error.

Best practices

Good constraint design habits include:

• Define a primary key on every table.
• Prefer stable primary keys.
• Add unique constraints for real business uniqueness rules.
• Use foreign keys to enforce relationships.
• Index foreign key columns when they are used in joins, filters, or parent deletes.
• Use NOT NULL for required fields.
• Use CHECK constraints for simple domain rules.
• Use DEFAULT constraints for safe initial values.
• Name constraints explicitly.
• Be careful with cascade delete.
• Use composite keys where they model the relationship clearly.
• Use filtered unique indexes for conditional uniqueness in SQL Server.
• Keep database constraints and application validation aligned.
• Test migrations against realistic data.
• Review constraints as part of schema design, not as an afterthought.
• Treat the database as the final guard for critical integrity rules.

Practical design example

A realistic order schema might look like this:

CREATE TABLE Customers
(
    CustomerId INT IDENTITY(1,1) NOT NULL,
    Email NVARCHAR(320) NOT NULL,
    FullName NVARCHAR(200) NOT NULL,
    CreatedAtUtc DATETIME2 NOT NULL
        CONSTRAINT DF_Customers_CreatedAtUtc DEFAULT SYSUTCDATETIME(),

    CONSTRAINT PK_Customers PRIMARY KEY (CustomerId),
    CONSTRAINT UQ_Customers_Email UNIQUE (Email)
);

CREATE TABLE Products
(
    ProductId INT IDENTITY(1,1) NOT NULL,
    Sku NVARCHAR(50) NOT NULL,
    Name NVARCHAR(200) NOT NULL,
    Price DECIMAL(18, 2) NOT NULL,

    CONSTRAINT PK_Products PRIMARY KEY (ProductId),
    CONSTRAINT UQ_Products_Sku UNIQUE (Sku),
    CONSTRAINT CK_Products_Price CHECK (Price >= 0)
);

CREATE TABLE Orders
(
    OrderId INT IDENTITY(1,1) NOT NULL,
    CustomerId INT NOT NULL,
    Status NVARCHAR(20) NOT NULL
        CONSTRAINT DF_Orders_Status DEFAULT 'Pending',
    CreatedAtUtc DATETIME2 NOT NULL
        CONSTRAINT DF_Orders_CreatedAtUtc DEFAULT SYSUTCDATETIME(),

    CONSTRAINT PK_Orders PRIMARY KEY (OrderId),
    CONSTRAINT FK_Orders_Customers
        FOREIGN KEY (CustomerId)
        REFERENCES Customers(CustomerId),
    CONSTRAINT CK_Orders_Status
        CHECK (Status IN ('Pending', 'Paid', 'Cancelled'))
);

CREATE TABLE OrderItems
(
    OrderItemId INT IDENTITY(1,1) NOT NULL,
    OrderId INT NOT NULL,
    ProductId INT NOT NULL,
    Quantity INT NOT NULL,
    UnitPrice DECIMAL(18, 2) NOT NULL,

    CONSTRAINT PK_OrderItems PRIMARY KEY (OrderItemId),
    CONSTRAINT FK_OrderItems_Orders
        FOREIGN KEY (OrderId)
        REFERENCES Orders(OrderId)
        ON DELETE CASCADE,
    CONSTRAINT FK_OrderItems_Products
        FOREIGN KEY (ProductId)
        REFERENCES Products(ProductId),
    CONSTRAINT CK_OrderItems_Quantity CHECK (Quantity > 0),
    CONSTRAINT CK_OrderItems_UnitPrice CHECK (UnitPrice >= 0)
);

CREATE INDEX IX_Orders_CustomerId
ON Orders(CustomerId);

CREATE INDEX IX_OrderItems_OrderId
ON OrderItems(OrderId);

CREATE INDEX IX_OrderItems_ProductId
ON OrderItems(ProductId);

This schema enforces:

• Unique customers by email.
• Unique products by SKU.
• Orders must belong to valid customers.
• Order items must belong to valid orders and products.
• Quantity and price must be valid.
• Deleting an order deletes its dependent order items.
• Foreign key columns are indexed for common joins.