UNIQUE, CHECK, NOT NULL, and DEFAULT constraints

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Constrain disposable rows and supply a default for an omitted value. Run changing examples only on a disposable training instance. The example uses a session-local object or rolls back its changes.

Run the example

CREATE TABLE #Rules (RuleID int NOT NULL PRIMARY KEY,
    Code nvarchar(20) NOT NULL UNIQUE, Quantity int NOT NULL CHECK (Quantity > 0),
    IsActive bit NOT NULL DEFAULT (1));
INSERT INTO #Rules (RuleID, Code, Quantity) VALUES (1, N'A', 2);
SELECT RuleID, Code, Quantity, IsActive FROM #Rules;
DROP TABLE #Rules;

Read it step by step

1. NOT NULL rejects missing required values; UNIQUE prevents duplicate Code values.
2. CHECK requires a positive Quantity for non-NULL inputs, and NOT NULL closes the NULL loophole.
3. Because INSERT omits IsActive, DEFAULT supplies 1.

Check the result

No canonical table or constraint changes.

Practice

Create a temp table with a positive Price, unique Code, and default bit flag; insert one valid row without the flag.

CREATE TABLE #Rules (Code nvarchar(20) NOT NULL UNIQUE,
    Price decimal(10,2) NOT NULL CHECK (Price > 0),
    IsActive bit NOT NULL DEFAULT (1));
INSERT INTO #Rules (Code, Price) VALUES (N'P', 12.50);
SELECT Code, Price, IsActive FROM #Rules;
DROP TABLE #Rules;