CREATE, ALTER and constraints

Enforce business rules in the data layer.

UnderstandStudy an examplePractise

Understand the concept

DDL creates or changes objects. NOT NULL requires a value; CHECK enforces a predicate; UNIQUE enforces uniqueness; PRIMARY KEY identifies a row; FOREIGN KEY validates relationships. DEFAULT supplies an omitted value; it does not universally reject an explicitly submitted NULL.

CHECK rejects FALSE, while some UNKNOWN outcomes caused by NULL can pass. A required positive amount therefore needs both NOT NULL and CHECK. ALTER can cause locking and conversion costs on large tables. Production migrations need a data-compatibility and rollback plan.

WHEN TO USE IT

Use constraints to prevent invalid data even when inserted outside the application.

COMMON PITFALL

Assuming CHECK(Price > 0) also forbids NULL.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

CREATE TABLE #Amounts (Id int NOT NULL PRIMARY KEY, Amount decimal(10,2) NOT NULL CHECK (Amount > 0));

Creates a required positive-amount rule in a temporary table.

This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.

02 / YOUR TURN

Try it yourself first.

Create a temporary table requiring a positive quantity.

Show the solution and explanation

Worked solution

CREATE TABLE #QuantityDemo (Id int NOT NULL PRIMARY KEY, Quantity int NOT NULL CHECK (Quantity > 0));

NOT NULL rejects missing values; CHECK rejects zero or negative quantities.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.