SQL for .NET developers Beginner

Constraints: NOT NULL, UNIQUE, CHECK, DEFAULT and foreign keys

Constraints are rules the database enforces on every insert and update: a value must exist, be unique, pass a check, get a default or point to a real row in another table. This lesson shows each kind with examples and explains why rules in the database beat rules only in C#.

What happens on INSERT
  1. StartINSERT arrivesA new row from your app, a script or an import
  2. Step 2DEFAULTs fill gapsColumns left out get their default values
  3. Step 3NOT NULL and CHECKEach value must exist and pass its rule
  4. Step 4UNIQUE and PRIMARY KEYNo other row may have the same key
  5. Step 5FOREIGN KEYReferenced parent row must exist
  6. EndRow savedOr the whole statement fails with an error

Why put rules in the database

Your C# validation protects one app. The database is shared by every app, script, report and admin who touches it. A constraint is checked on every write, so bad data cannot slip in through a forgotten code path or a manual UPDATE.

Constraints also help the query optimizer: a trusted foreign key or unique constraint tells SQL Server facts it can use to simplify plans.

The six kinds

  • NOT NULL: the column must always have a value.
  • PRIMARY KEY: unique, not null, and identifies each row (one per table).
  • UNIQUE: no two rows may share the value, for example an email address.
  • CHECK: the value must pass a condition, such as Price >= 0.
  • DEFAULT: the value used when an insert leaves the column out.
  • FOREIGN KEY: the value must exist as a key in another table.

Example: orders with every kind of constraint

SQL
CREATE TABLE Customers (
    CustomerId int IDENTITY PRIMARY KEY,
    Email      nvarchar(256) NOT NULL CONSTRAINT UQ_Customers_Email UNIQUE,
    Name       nvarchar(200) NOT NULL
);

CREATE TABLE Orders (
    OrderId    int IDENTITY PRIMARY KEY,
    CustomerId int NOT NULL
        CONSTRAINT FK_Orders_Customers REFERENCES Customers(CustomerId)
        ON DELETE NO ACTION,
    Status     varchar(20) NOT NULL
        CONSTRAINT DF_Orders_Status DEFAULT 'New'
        CONSTRAINT CK_Orders_Status CHECK (Status IN ('New','Paid','Shipped','Cancelled')),
    Total      decimal(18,2) NOT NULL CONSTRAINT CK_Orders_Total CHECK (Total >= 0),
    OrderedAt  datetime2 NOT NULL CONSTRAINT DF_Orders_OrderedAt DEFAULT SYSUTCDATETIME()
);

Foreign keys and deletes

When a parent row is deleted, the foreign key decides what happens to its children.

  • NO ACTION (the default): the delete fails while children exist. Safest choice.
  • CASCADE: children are deleted too. Convenient for owned data such as order lines, dangerous for shared data.
  • SET NULL or SET DEFAULT: children stay but lose their link.
  • SQL Server does not index foreign key columns automatically; add an index on Orders(CustomerId) yourself for fast joins and deletes.

Naming and errors

Give constraints names such as CK_Orders_Total. The name appears in the error message when a rule is broken, so the problem is obvious, and named constraints are easy to drop or change in migrations.

In .NET a broken constraint arrives as a SqlException (or DbUpdateException in EF Core) with error 547 for check and foreign key violations and 2627 or 2601 for unique violations. Catch these to show a friendly message.

Interview question

What is the difference between a PRIMARY KEY and a UNIQUE constraint?

Both reject duplicate values. A table has only one primary key, its columns can never be NULL, and it identifies the row (it is usually the clustered index). A table can have many unique constraints, and a unique column can hold NULL (SQL Server allows one NULL per unique constraint). Use the primary key for the row's identity and unique constraints for other natural keys such as an email address.

Your progress is saved only in this browser.

Want the full details? Read the official Microsoft documentation for this topic.