Adding/Changing Constraints and Table Redefinition

The data we'll use — legacy_user

Tables in production often turn out to need constraints that weren't included in the original design.

Things like "attach a default value to this column," "make email duplication-free (UNIQUE)," or "mark this required field as NOT NULL."

This article covers adding columns and retrofitting default values, and the table-rebuild procedure for adding constraints to an existing table.

Before the exercises, take a look at the column definitions and sample data of the legacy_user table.

① Run PRAGMA table_info(legacy_user); to check column names, types, primary key, NOT NULL, and default values.

② Run SELECT * FROM legacy_user; to preview all rows.

ALTER TABLE ADD COLUMN — retrofit a column and default value

To add a new column to an existing table, use ALTER TABLE table ADD COLUMN col_name type constraint.

Attaching DEFAULT value sets the default value used when the column is omitted, and at ALTER time the value also fills in for every existing row.

ADD COLUMN even accepts NOT NULL when combined with DEFAULT (existing rows are filled with the default, so they don't violate NOT NULL).

On the other hand, ADD COLUMN can't directly attach UNIQUE or a NOT NULL without a default.

Those require a "table rebuild."

-- Add a column with DEFAULT (use a throwaway table to see the behavior)
DROP TABLE IF EXISTS alter_demo;
CREATE TABLE alter_demo(id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO alter_demo VALUES (1,'Alice'),(2,'Bob');

-- Add created_on with a DEFAULT → existing rows get the value too
ALTER TABLE alter_demo ADD COLUMN created_on TEXT DEFAULT '2024-01-01';

-- NOT NULL is also fine when combined with DEFAULT
ALTER TABLE alter_demo ADD COLUMN status TEXT NOT NULL DEFAULT 'active';

SELECT * FROM alter_demo ORDER BY id;

Imagine the requirement: "add a status column to the user table and treat all existing users as active." (Run it correctly and the explanation appears.)

① Add a TEXT column named status to legacy_user with a default value of 'active'.

② Then run SELECT * FROM legacy_user; and confirm that the status column on the existing 2 rows is filled with the default value.

Adding constraints after the fact means rebuilding the table

In MySQL you can attach constraints directly to an existing table, like ALTER TABLE legacy_user ADD CONSTRAINT uq_email UNIQUE (email);.

In this course's console, retrofitting UNIQUE or NOT NULL onto an existing table goes through the table-rebuild procedure: "create a new table with the constraints you want, copy the existing data over, and replace the old table."

MySQL / PostgreSQL also perform this rebuild pattern internally for some operations (like column type changes), depending on the tool.

The procedure follows the figure below: turn off the foreign-key check, then CREATE the new table → copy all rows → DROP the old table → RENAME the new table → restore the check.

About MySQL's ADD CONSTRAINT / AUTO_INCREMENT / column comments

The ALTER TABLE ... ADD CONSTRAINT, AUTO_INCREMENT, and column comment (COMMENT '...') syntax shown below is MySQL syntax.

The browser console in this course can't run them, so these two code blocks are read-only (don't try to execute them).

In this course's console, retrofit constraints via the table rebuild in the section below, and use INTEGER PRIMARY KEY for auto-numbering.

-- MySQL syntax (don't run this in the course's console — just read it)

-- MySQL: attach a UNIQUE constraint directly to an existing table
ALTER TABLE legacy_user ADD CONSTRAINT uq_email UNIQUE (email);

-- MySQL: change to NOT NULL via MODIFY
ALTER TABLE legacy_user MODIFY email VARCHAR(255) NOT NULL;

-- MySQL: auto-numbering is the AUTO_INCREMENT column attribute; descriptions are column comments
CREATE TABLE member (
  member_id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL UNIQUE COMMENT 'login email'
);

-- In this course's console, get the AUTO_INCREMENT equivalent via INTEGER PRIMARY KEY
CREATE TABLE member (
  member_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

-- Skeleton of the table-rebuild (use throwaway table rebuild_demo to see the behavior)
DROP TABLE IF EXISTS rebuild_demo;
CREATE TABLE rebuild_demo(id INTEGER PRIMARY KEY, email TEXT);
INSERT INTO rebuild_demo VALUES (1,'a@example.com'),(2,'b@example.com');

-- ① Disable the foreign-key check
PRAGMA foreign_keys=OFF;

-- ② New table with desired constraints (email NOT NULL UNIQUE)
CREATE TABLE rebuild_demo_new(
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

-- ③ Copy existing data over
INSERT INTO rebuild_demo_new(id, email)
SELECT id, email FROM rebuild_demo;

-- ④ Drop the old table, ⑤ rename the new one to the old name
DROP TABLE rebuild_demo;
ALTER TABLE rebuild_demo_new RENAME TO rebuild_demo;

-- ⑥ Restore the foreign-key check
PRAGMA foreign_keys=ON;

-- Confirm the constraints are in place (duplicate email will now error)
SELECT sql FROM sqlite_master WHERE name='rebuild_demo';

Imagine the requirement: "change the email column on legacy_user to required and duplication-free (NOT NULL and UNIQUE)." Run the article's main table-rebuild procedure, step by step, exactly as shown in the diagram above.

① Disable the foreign-key check (PRAGMA foreign_keys=OFF;).

② CREATE a new table named legacy_user_new with id (INTEGER primary key), name (TEXT), and email (TEXT, NOT NULL, UNIQUE).

③ Copy every row from the existing table with INSERT INTO legacy_user_new(...) SELECT ... FROM legacy_user;.

④ DROP the old legacy_user and RENAME legacy_user_new to legacy_user.

⑤ Restore the foreign-key check to ON.

⑥ Finally, run SELECT sql FROM sqlite_master WHERE name='legacy_user'; and confirm the new schema contains UNIQUE / NOT NULL.

Reproduce the same rebuild on a throwaway table and experience the new schema's UNIQUE constraint actually blocking a duplicate. This exercise is self-contained and uses a dedicated table email_check.

① DROP TABLE IF EXISTS email_check; then CREATE it with id (INTEGER primary key) and email (TEXT, NOT NULL, UNIQUE).

② INSERT one row (1, 'a@example.com') into email_check.

③ Then INSERT another row (2, 'a@example.com') whose email value already exists. It violates the UNIQUE constraint, so an error is the correct outcome for the second INSERT.