DDL — CREATE TABLE, ALTER TABLE and Constraints

DDL (Data Definition Language) defines the database structure — tables, columns, constraints, and relationships. Well-designed DDL includes proper constraints that enforce data integrity at the database level (not just the application level). A FOREIGN KEY constraint prevents orphaned records; a CHECK constraint prevents invalid enum values; a UNIQUE constraint prevents duplicate slugs. These constraints protect the database even when code has bugs or when data is modified directly through SSMS.

CREATE TABLE with Constraints #

-- ── Users table (simplified — EF Core Identity manages the full schema) ────
CREATE TABLE dbo.Users (
    Id           NVARCHAR(450)  NOT NULL,
    Email        NVARCHAR(256)  NOT NULL,
    DisplayName  NVARCHAR(100)  NOT NULL,
    AvatarUrl    NVARCHAR(500)  NULL,
    IsActive     BIT            NOT NULL DEFAULT 1,
    CreatedAt    DATETIME2(7)   NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT PK_Users PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT UQ_Users_Email UNIQUE (Email)
);
GO

-- ── Categories table ─────────────────────────────────────────────────────
CREATE TABLE dbo.Categories (
    Id          INT          NOT NULL IDENTITY(1,1),
    Name        NVARCHAR(100) NOT NULL,
    Slug        VARCHAR(100)  NOT NULL,
    Description NVARCHAR(500) NULL,
    CONSTRAINT PK_Categories PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT UQ_Categories_Slug UNIQUE (Slug)
);
GO

-- ── Posts table ──────────────────────────────────────────────────────────
CREATE TABLE dbo.Posts (
    Id           INT            NOT NULL IDENTITY(1,1),
    AuthorId     NVARCHAR(450)  NOT NULL,
    Title        NVARCHAR(200)  NOT NULL,
    Slug         VARCHAR(200)   NOT NULL,
    Body         NVARCHAR(MAX)  NOT NULL,
    Excerpt      NVARCHAR(500)  NULL,
    CoverImageUrl NVARCHAR(500) NULL,
    Status       VARCHAR(20)    NOT NULL DEFAULT 'draft',
    ViewCount    INT            NOT NULL DEFAULT 0,
    IsPublished  BIT            NOT NULL DEFAULT 0,
    IsFeatured   BIT            NOT NULL DEFAULT 0,
    PublishedAt  DATETIME2(7)   NULL,
    CreatedAt    DATETIME2(7)   NOT NULL DEFAULT SYSUTCDATETIME(),
    UpdatedAt    DATETIME2(7)   NOT NULL DEFAULT SYSUTCDATETIME(),
    CONSTRAINT PK_Posts    PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT UQ_Posts_Slug UNIQUE (Slug),
    CONSTRAINT FK_Posts_Users FOREIGN KEY (AuthorId)
        REFERENCES dbo.Users (Id)
        ON DELETE NO ACTION   -- do not delete posts when user is deleted
        ON UPDATE NO ACTION,
    CONSTRAINT CHK_Posts_Status CHECK (
        Status IN ('draft', 'review', 'published', 'archived')
    ),
    CONSTRAINT CHK_Posts_ViewCount CHECK (ViewCount >= 0)
);
GO

-- ── Junction table: PostTags ──────────────────────────────────────────────
CREATE TABLE dbo.Tags (
    Id    INT          NOT NULL IDENTITY(1,1),
    Name  NVARCHAR(50) NOT NULL,
    Slug  VARCHAR(50)  NOT NULL,
    CONSTRAINT PK_Tags      PRIMARY KEY CLUSTERED (Id),
    CONSTRAINT UQ_Tags_Slug UNIQUE (Slug)
);
GO

CREATE TABLE dbo.PostTags (
    PostId INT NOT NULL,
    TagId  INT NOT NULL,
    CONSTRAINT PK_PostTags PRIMARY KEY CLUSTERED (PostId, TagId),  -- composite PK
    CONSTRAINT FK_PostTags_Posts FOREIGN KEY (PostId)
        REFERENCES dbo.Posts (Id) ON DELETE CASCADE,  -- delete tags when post deleted
    CONSTRAINT FK_PostTags_Tags FOREIGN KEY (TagId)
        REFERENCES dbo.Tags  (Id) ON DELETE CASCADE
);
GO

-- ── ALTER TABLE examples ──────────────────────────────────────────────────
-- Add a column:
ALTER TABLE dbo.Posts
    ADD WordCount INT NULL;
GO

-- Add a constraint:
ALTER TABLE dbo.Posts
    ADD CONSTRAINT CHK_Posts_WordCount CHECK (WordCount IS NULL OR WordCount >= 0);
GO

-- Drop a column (must drop constraints first):
ALTER TABLE dbo.Posts DROP CONSTRAINT CHK_Posts_WordCount;
ALTER TABLE dbo.Posts DROP COLUMN WordCount;
GO

Common Mistakes #

Mistake 1 — Unnamed constraints (system-generated names are unmaintainable) #

❌ Wrong — PRIMARY KEY (Id) without a name; error messages show cryptic auto-generated names.

✅ Correct — always name constraints: CONSTRAINT PK_Posts PRIMARY KEY CLUSTERED (Id).

Mistake 2 — ON DELETE CASCADE on critical parent-child relationships (silent mass deletion) #

❌ Wrong — FK_Posts_Users ON DELETE CASCADE; deleting a user silently deletes all their posts.

✅ Correct — use ON DELETE NO ACTION for important relationships; handle orphan cleanup explicitly in application logic.