Constraints and Schema Changes: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, DEFAULT and ALTER TABLE

Version note: The SQL (Structured Query Language) queries in this post were run on SQL Server 2025 (17.0, Cumulative Update 10); the PostgreSQL comparisons were run on PostgreSQL 18.6.

Guess First: Are Two NULLs the Same Value?

Bookstore starts selling gift cards. The rule: every card's code is unique, a customer can have at most one card, but a card may not be assigned to anyone yet. We build the table accordingly:

CREATE TABLE dbo.gift_cards (
    gift_card_id INT IDENTITY(1,1) NOT NULL,
    code         VARCHAR(20)       NOT NULL,
    customer_id  INT               NULL,
    CONSTRAINT PK_gift_cards PRIMARY KEY (gift_card_id),
    CONSTRAINT UQ_gift_cards_code UNIQUE (code),
    CONSTRAINT UQ_gift_cards_customer UNIQUE (customer_id)
);

Now we add three cards: one assigned to Ada, two not assigned to anyone yet. How many rows get inserted?

INSERT INTO dbo.gift_cards (code, customer_id) VALUES ('GIFT-0001', 1);
INSERT INTO dbo.gift_cards (code, customer_id) VALUES ('GIFT-0002', NULL);
INSERT INTO dbo.gift_cards (code, customer_id) VALUES ('GIFT-0003', NULL);
SELECT gift_card_id, code, customer_id FROM dbo.gift_cards;

(1 rows affected)

(1 rows affected)
Msg 2627, Level 14, State 1, Server b8be943222fa, Line 3
Violation of UNIQUE KEY constraint 'UQ_gift_cards_customer'. Cannot insert duplicate key in object 'dbo.gift_cards'. The duplicate key value is (<NULL>).
The statement has been terminated.
gift_card_id  code       customer_id
------------  ---------  -----------
1             GIFT-0001  1
2             GIFT-0002  NULL

(2 rows affected)

SQL Server counted the second NULL as a "duplicate value". Yet NULL means "unknown", and there is no reason to claim that two unknowns are equal. The same three INSERTs add three rows on PostgreSQL 18.6. Which one is right? The answer is "both, because for years the standard left this to the product". In this post we cover constraints, the rules the database enforces on its own: NOT NULL, primary keys, foreign keys, UNIQUE, CHECK, DEFAULT, and how to add them safely to an existing table with ALTER TABLE.

What Is a Constraint and Why Name It?

A constraint is a rule that the data in a table must satisfy at all times. The difference from validation in the application layer is that it applies no matter which path writes to the database. An INSERT coming from the API (Application Programming Interface), an admin script, an ETL (Extract, Transform, Load) job or an SSMS (SQL Server Management Studio) window hits the same rule. The simplest one is NOT NULL: the column cannot be left empty.

If you do not name a constraint, SQL Server makes up a name:

CREATE TABLE dbo.coupons (
    coupon_id INT PRIMARY KEY,
    code      VARCHAR(20) UNIQUE,
    percent_off TINYINT CHECK (percent_off BETWEEN 1 AND 90)
);
SELECT name, type_desc FROM sys.objects WHERE parent_object_id = OBJECT_ID('dbo.coupons') ORDER BY type_desc;

name                            type_desc
------------------------------  ----------------------
CK__coupons__percent__02084FDA  CHECK_CONSTRAINT
PK__coupons__58CF638990E1C041   PRIMARY_KEY_CONSTRAINT
UQ__coupons__357D4CF9120A0819   UNIQUE_CONSTRAINT

(3 rows affected)

The hex part at the end is generated differently in every database. A constraint called PK__coupons__58CF638990E1C041 in development carries another name in production, and a migration script that says "drop this constraint" runs in one environment and fails in the other. Error messages come with the name too; CK_reviews_rating is readable, CK__reviews__rating__5EBF139D is not. In Bookstore every constraint is named with a PK_, FK_, UQ_, CK_ or DF_ prefix plus the table name.

PRIMARY KEY: Single Column, Composite, and Who Generates the Value?

A primary key identifies a row uniquely and brings two rules: values do not repeat and none of them is NULL. By default SQL Server creates a clustered index for the primary key; the physical layout of the table depends on this decision, with details in the "SQL Server Derinlemesine" (SQL Server in Depth) series. A key can consist of more than one column too. Bookstore's book_authors is an example: PRIMARY KEY (book_id, author_id) prevents linking the same author to the same book twice, while a book can still have several authors.

For single-column keys, who generates the value is a design decision. There are three common options. IDENTITY is a counter bound to the table; the value is generated during INSERT and is not given back even if the transaction is rolled back:

CREATE TABLE dbo.id_demo (id INT IDENTITY(1,1) PRIMARY KEY, note VARCHAR(10));
INSERT INTO dbo.id_demo (note) VALUES ('first');
BEGIN TRANSACTION;
    INSERT INTO dbo.id_demo (note) VALUES ('rolled');
ROLLBACK TRANSACTION;
INSERT INTO dbo.id_demo (note) VALUES ('third');
SELECT id, note FROM dbo.id_demo;

id  note
--  -----
1   first
3   third

Number 2 is gone. Identity values do not guarantee a gapless sequence; using identity for a number that must be gapless, such as an invoice number, is a mistake. SEQUENCE, on the other hand, is a counter independent of any table, part of the standard since SQL:2003, and you can fetch its value before the INSERT:

CREATE SEQUENCE dbo.invoice_number_seq AS INT START WITH 1000 INCREMENT BY 1;
SELECT NEXT VALUE FOR dbo.invoice_number_seq AS first_value,
       NEXT VALUE FOR dbo.invoice_number_seq AS same_row_value;
SELECT NEXT VALUE FOR dbo.invoice_number_seq AS next_call;

first_value  same_row_value
-----------  --------------
1000         1000

(1 rows affected)
next_call
---------
1001

(1 rows affected)

NEXT VALUE FOR called twice in the same row returns the same value; that is SQL Server's documented behaviour. The third option is UNIQUEIDENTIFIER: the application can generate the value, several systems can generate ids without colliding, but it is 16 bytes and random GUIDs (Globally Unique Identifiers) cause page splits in a clustered index. The deep comparison of these three options is in post 2 of the "SQL Server Derinlemesine" series; designing ids exposed to the outside is in the Solution Pattern series post "Id'yi Dışarıya Açmak: int Identity, GUID v7 ve Public Id Ayrımı" (exposing ids).

FOREIGN KEY and ON DELETE

A foreign key requires the value in a column to exist in the key of another table. Let's build the book editions table again, this time with its constraints:

CREATE TABLE dbo.book_editions (
    edition_id    INT IDENTITY(1,1) NOT NULL,
    book_id       INT               NOT NULL,
    edition_label NVARCHAR(100)     NOT NULL,
    isbn          CHAR(13)          NOT NULL,
    page_count    SMALLINT          NULL,
    list_price    DECIMAL(10,2)     NOT NULL,
    is_hardcover  BIT               NOT NULL CONSTRAINT DF_book_editions_is_hardcover DEFAULT (0),
    created_at    DATETIME2(0)      NOT NULL CONSTRAINT DF_book_editions_created_at DEFAULT (SYSUTCDATETIME()),
    CONSTRAINT PK_book_editions PRIMARY KEY (edition_id),
    CONSTRAINT FK_book_editions_books FOREIGN KEY (book_id) REFERENCES dbo.books (book_id),
    CONSTRAINT UQ_book_editions_isbn UNIQUE (isbn),
    CONSTRAINT CK_book_editions_list_price CHECK (list_price >= 0),
    CONSTRAINT CK_book_editions_page_count CHECK (page_count > 0)
);
INSERT INTO dbo.book_editions (book_id, edition_label, isbn, page_count, list_price)
VALUES (75, N'İnce Memed 1, pocket edition', '9798900000751', 436, 289.90);

Trying to add an edition for a book that does not exist:

INSERT INTO dbo.book_editions (book_id, edition_label, isbn, list_price)
VALUES (999, N'Ghost Edition', '9798900009999', 100.00);

Msg 547, Level 16, State 1, Server b8be943222fa, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_book_editions_books". The conflict occurred in database "sqlt_04", table "dbo.books", column 'book_id'.
The statement has been terminated.

The rule works in both directions. If you now try to delete Dune, which has an edition, the DELETE is the one blocked: The DELETE statement conflicted with the REFERENCE constraint "FK_book_editions_books". What happens to child rows when the parent is deleted is set by ON DELETE. The default is NO ACTION: it rejects deleting a parent while child rows exist. CASCADE deletes the children too, SET NULL and SET DEFAULT empty the foreign key column or reset it to its default. In Bookstore order_items is tied to the order with CASCADE, and payments with NO ACTION. Let's see the result of this mix on two orders. Order 6 was cancelled and has no payment:

BEGIN TRANSACTION;
SELECT COUNT(*) AS items_before FROM dbo.order_items WHERE order_id = 6;
DELETE FROM dbo.orders WHERE order_id = 6;
SELECT COUNT(*) AS items_after FROM dbo.order_items WHERE order_id = 6;
ROLLBACK TRANSACTION;

items_before
------------
1

(1 rows affected)

(1 rows affected)
items_after
-----------
0

(1 rows affected)

The order was deleted and its item went with it. Order 263, however, is a paid order, and the same DELETE stopped there:

Msg 547, Level 16, State 1, Server b8be943222fa, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_payments_orders". The conflict occurred in database "sqlt_04", table "dbo.payments", column 'order_id'.
The statement has been terminated.

That is a deliberate design. Order items are part of the order and mean nothing without it; a payment is an accounting record and must not vanish silently while an order is deleted "by accident". Use CASCADE only when the child row has no meaning at all without its parent.

CHECK and DEFAULT

CHECK is a condition a row must satisfy. In Bookstore stock cannot go negative and a rating must be between 1 and 5. Let's try to reduce the stock as if 100 copies of Dune were sold:

UPDATE dbo.stock_items SET quantity_on_hand = quantity_on_hand - 100 WHERE book_id = 26;

Msg 547, Level 16, State 1, Server b8be943222fa, Line 1
The UPDATE statement conflicted with the CHECK constraint "CK_stock_items_quantity_on_hand". The conflict occurred in database "sqlt_04", table "dbo.stock_items", column 'quantity_on_hand'.
The statement has been terminated.

Application code written as "read the stock first, decrement if it is enough" can push stock below zero under two concurrent requests; this constraint is the last line of defence. The same goes for ratings: a review arriving with rating = 6 hits CK_reviews_rating. Now a subtle point. An edition with a negative price is rejected, but what about an audiobook edition whose page count is NULL?

INSERT INTO dbo.book_editions (book_id, edition_label, isbn, page_count, list_price)
VALUES (26, N'Dune, audio', '9798900000270', NULL, 520.00);
SELECT edition_id, edition_label, page_count, is_hardcover FROM dbo.book_editions;

edition_id  edition_label                 page_count  is_hardcover
----------  ----------------------------  ----------  ------------
1           İnce Memed 1, pocket edition  436         0
5           Dune, audio                   NULL        0

(2 rows affected)

Accepted. NULL > 0 is neither true nor false but "unknown", and CHECK only rejects when the result is false (FALSE). WHERE, on the other hand, only lets true (TRUE) through. The same three-valued logic, two different outcomes in two places. If you want NULL rejected as well, make the column NOT NULL. One more thing: edition_id jumped from 1 to 5, because every failed INSERT consumed an identity value. We did not write is_hardcover; DEFAULT put in 0. DEFAULT only kicks in when the column is not written at all; if you write NULL explicitly, NULL is stored.

UNIQUE and NULL: SQL Server, the Standard and PostgreSQL

Back to the opening question. Until SQL:2023 the standard left it to the product whether a UNIQUE column may contain more than one NULL. SQL:2023 made it selectable: UNIQUE NULLS DISTINCT (NULLs count as distinct from each other, any number of NULLs allowed) and UNIQUE NULLS NOT DISTINCT (NULLs count as equal, a single NULL). PostgreSQL has supported both since version 15, and its default is NULLS DISTINCT. The same experiment on PostgreSQL 18.6:

 gift_card_id |   code    | customer_id
--------------+-----------+-------------
            1 | GIFT-0001 |           1
            2 | GIFT-0002 |
            3 | GIFT-0003 |
(3 rows)

Declare the column with UNIQUE NULLS NOT DISTINCT and PostgreSQL behaves like SQL Server: Key (customer_id)=(null) already exists. SQL Server 2025, however, does not recognise this syntax (Incorrect syntax near 'NULLS'.); its UNIQUE constraint always allows a single NULL. The SQL Server solution for the rule "unique if present, free if absent" is a filtered unique index:

ALTER TABLE dbo.gift_cards DROP CONSTRAINT UQ_gift_cards_customer;
CREATE UNIQUE INDEX UX_gift_cards_customer
    ON dbo.gift_cards (customer_id)
    WHERE customer_id IS NOT NULL;
INSERT INTO dbo.gift_cards (code, customer_id) VALUES ('GIFT-0003', NULL);
INSERT INTO dbo.gift_cards (code, customer_id) VALUES ('GIFT-0004', NULL);
INSERT INTO dbo.gift_cards (code, customer_id) VALUES ('GIFT-0005', 1);
SELECT gift_card_id, code, customer_id FROM dbo.gift_cards;

Msg 2601, Level 14, State 1, Server b8be943222fa, Line 7
Cannot insert duplicate key row in object 'dbo.gift_cards' with unique index 'UX_gift_cards_customer'. The duplicate key value is (1).
The statement has been terminated.
gift_card_id  code       customer_id
------------  ---------  -----------
1             GIFT-0001  1
2             GIFT-0002  NULL
4             GIFT-0003  NULL
5             GIFT-0004  NULL

(4 rows affected)

Exactly what we wanted: NULLs are free, and a second card for Ada was rejected. Because the index only contains rows that satisfy its WHERE condition, uniqueness is only checked among them. A filtered index has a requirement: QUOTED_IDENTIFIER and ANSI_NULLS must be on in the session, otherwise every INSERT into that table fails. That is why we added the -I flag to sqlcmd in the previous post. The same technique is used in soft delete for the rule "email must be unique among non-deleted records"; it is covered in the Solution Pattern series post "Soft Delete'i Doğru Yapmak: IsDeleted, DeletedAt, Filtered Unique Index ve Archive Tablosu" (soft delete done right).

Changing the Schema with ALTER TABLE

Let's add a mandatory column to a populated table. Where would the value for the existing 40 customers come from?

ALTER TABLE dbo.customers ADD birth_date DATE NOT NULL;

Msg 4901, Level 16, State 1, Server b8be943222fa, Line 1
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'birth_date' cannot be added to non-empty table 'customers' because it does not satisfy these conditions.

The error message lists the options: either the column accepts NULL, or it has a DEFAULT. For loyalty points zero is a sensible default:

ALTER TABLE dbo.customers ADD loyalty_points INT NOT NULL
    CONSTRAINT DF_customers_loyalty_points DEFAULT (0);
GO
SELECT TOP (3) customer_id, first_name, loyalty_points FROM dbo.customers ORDER BY customer_id;

customer_id  first_name  loyalty_points
-----------  ----------  --------------
1            Ada         0
2            Alan        0
3            Grace       0

(3 rows affected)

The GO here is not redundant: if you write the ALTER TABLE and a SELECT that uses the new column in the same batch, you get Invalid column name 'loyalty_points', because the column does not exist yet when the batch is compiled. Narrowing a column can clash with the data too. Trying to squeeze phone numbers into VARCHAR(10), ALTER TABLE dbo.customers ALTER COLUMN phone VARCHAR(10) NULL stops with Msg 2628, "String or binary data would be truncated". SQL Server does not truncate data silently.

The safe order for a schema change on a large table in production is usually "expand, migrate, contract": first add the new column as nullable; let the new version of the application write to both columns; fill the old rows in small batches (backfill); then add the constraint and make the column NOT NULL; finally drop the old column. Every step can be rolled back and none of them locks the table for long. How to split these steps into separate Entity Framework Core (EF Core) migrations is shown in the EF Core series post "EF Core Migrations: migrations add, database update, Script ve Bundle".

WITH NOCHECK and Untrusted Constraints

A new business rule arrives: no book in the catalog below 250 TRY (Turkish lira). Let's add the constraint:

ALTER TABLE dbo.books ADD CONSTRAINT CK_books_min_price CHECK (list_price >= 250);

Msg 547, Level 16, State 1, Server b8be943222fa, Line 1
The ALTER TABLE statement conflicted with the CHECK constraint "CK_books_min_price". The conflict occurred in database "sqlt_04", table "dbo.books", column 'list_price'.

The catalog has 9 books under 250 TRY, and since SQL Server checks existing data too, the constraint cannot be added. The most frequently suggested "fix" on the internet is WITH NOCHECK:

ALTER TABLE dbo.books WITH NOCHECK ADD CONSTRAINT CK_books_min_price CHECK (list_price >= 250);
SELECT name, is_disabled, is_not_trusted FROM sys.check_constraints WHERE parent_object_id = OBJECT_ID('dbo.books');

name                 is_disabled  is_not_trusted
-------------------  -----------  --------------
CK_books_min_price   0            1
CK_books_list_price  0            0
CK_books_page_count  0            0

(3 rows affected)

The constraint was added and will be enforced for future writes, but is_not_trusted = 1. SQL Server knows that rows violating this rule sit in the table and labels the constraint "not trusted". The cost is not only the old data but the query plan as well. A trusted constraint gives the optimizer information. While CK_books_list_price (price cannot be below zero) is trusted, the plan of "count books with a price below zero" is:

SET SHOWPLAN_TEXT ON;
GO
SELECT COUNT(*) AS n FROM dbo.books WHERE list_price < 0 OPTION (RECOMPILE);
GO
SET SHOWPLAN_TEXT OFF;

StmtText
--------
  |--Constant Scan(VALUES:(((0))))

The table is not touched at all; thanks to the constraint the optimizer knows the answer is zero. If you disable the same constraint with NOCHECK and re-enable it with plain CHECK CONSTRAINT, that is, without validating the existing data, is_not_trusted becomes 1 and the same query starts scanning books: Table 'books'. Scan count 1, logical reads 3. The same applies to foreign keys; an untrusted foreign key keeps the optimizer from removing unnecessary joins from the plan. That is why scripts that disable constraints before a bulk load and re-enable them afterwards are dangerous. To make a constraint trusted again you have to have the existing data validated; the double CHECK is not a typo:

ALTER TABLE dbo.order_items WITH CHECK CHECK CONSTRAINT FK_order_items_books;
SELECT name, is_disabled, is_not_trusted FROM sys.foreign_keys WHERE name = 'FK_order_items_books';

name                  is_disabled  is_not_trusted
--------------------  -----------  --------------
FK_order_items_books  0            0

(1 rows affected)

The right solution for the minimum price rule is not NOCHECK: first fix the prices of the 9 books together with the business, then add the constraint the normal way.

In Production

Adding a constraint takes locks while it scans the table; on a table with millions of rows that can take minutes. On SQL Server 2025 Enterprise edition, ADD CONSTRAINT can run as a resumable operation; on Standard, plan a maintenance window. Check regularly which constraints are untrusted: every row with is_not_trusted = 1 in sys.check_constraints and sys.foreign_keys means either old data that needs cleaning or a forgotten load script. Recognise constraint errors on the application side: Msg 547 (foreign key or check), 2627 (unique constraint) and 2601 (unique index) should reach the user as a meaningful 409 or 422, not a 500. You can do that mapping with the approach in the ASP.NET Core Web API series post "ASP.NET Core'da Global Hata Yönetimi: Exception Middleware ve ProblemDetails" (global error handling).

Common Pitfalls

• Not naming constraints, so migration scripts run into different names in different environments.
• Forgetting that CHECK lets NULL through. If the rule applies to NULL too, make the column NOT NULL.
• Assuming a UNIQUE column in SQL Server accepts more than one NULL; the solution is a filtered unique index.
• Making every foreign key ON DELETE CASCADE. When you delete a customer you do not want their orders, payments and reviews deleted silently.
• Assuming identity values will be gapless; rollbacks and failed INSERTs consume values.
• Believing constraints added WITH NOCHECK, or disabled with NOCHECK and simply re-enabled, "work"; untrusted constraints do not protect old data and do not help the optimizer.
• Trying to add a NOT NULL column without a DEFAULT to a populated table, or changing a column type in a single step on a large table.

A Small Experiment

★☆☆ Add CONSTRAINT CK_book_editions_label CHECK (LEN(edition_label) >= 3) to book_editions and try an INSERT that breaks the rule. Read the constraint name in the error message.

★★☆ Implement the rule "a phone number must be unique if present" for customers.phone with a filtered unique index. Then try writing the same phone to two customers. Why does a UNIQUE constraint not work for this rule?

★★★ Redefine the foreign key from reviews to customers with ON DELETE CASCADE, then inside a transaction try to delete a customer with no orders and a customer who has reviews, and ROLLBACK. Which delete failed and why? Which foreign key path blocked it?

The schema is built and the rules are in place. In the next post we move to the reading side of data: SELECT, WHERE and ORDER BY, SQL's logical query processing order, and writing the "newest 20 books, page 3" query correctly with TOP and OFFSET/FETCH.