ALTER TABLE

ALTER TABLE

What it means

ALTER TABLE modifies an existing table’s structure — adding, changing, or dropping columns and constraints — without recreating the table or losing its existing data (for non-destructive changes).

Examples

ALTER TABLE Employees ADD Email NVARCHAR(255);

ALTER TABLE Employees ALTER COLUMN Email NVARCHAR(320) NOT NULL;

ALTER TABLE Employees DROP COLUMN MiddleName;

ALTER TABLE Employees ADD CONSTRAINT UQ_Email UNIQUE (Email);

ALTER TABLE Orders ADD CONSTRAINT FK_Orders_Customers
    FOREIGN KEY (CustomerId) REFERENCES Customers(Id);

Common mistake

Adding a NOT NULL column to a table that already has rows, without a DEFAULT value — SQL Server has nothing to put in that column for existing rows, and the statement fails outright.

-- Error if the table already has rows: Cannot insert the value NULL into
-- column 'Email' ... does not allow nulls
ALTER TABLE Employees ADD Email NVARCHAR(255) NOT NULL;

-- Fix: provide a DEFAULT so existing rows get a valid value
ALTER TABLE Employees ADD Email NVARCHAR(255) NOT NULL DEFAULT 'unknown@example.com';

Quick practice

1. What does ALTER TABLE ... ADD do?

   Answer

   Adds a new column (or constraint) to an existing table.

2. Why does adding a NOT NULL column without a default fail on a table that already has rows?

   Answer

   SQL Server would have no value to place in that new column for the existing rows, and NOT NULL forbids leaving it empty, so the statement is rejected unless a DEFAULT is supplied.

3. What clause removes a column entirely from a table?

   Answer

   ALTER TABLE TableName DROP COLUMN ColumnName;