SQL ADD COLUMN: Inspect Existing Rows and an Explicit Default

The practice task

Add a severity field to a fictional activity log. Existing entries should read as info, while a new entry can explicitly supply warning. The example uses SQLite table-alteration syntax.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE activity(id INTEGER, message TEXT);
INSERT INTO activity VALUES (1,'created'),(2,'checked');
ALTER TABLE activity ADD COLUMN severity TEXT NOT NULL DEFAULT 'info';
INSERT INTO activity(id,message,severity) VALUES (3,'needs review','warning');

SELECT id, message, severity FROM activity ORDER BY id;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

The two existing rows read with severity info. The new row contains its supplied warning value. The default is a schema rule for the added column; an explicit value can differ from it as long as it satisfies the column constraints.

A default should represent the intended meaning for older records. If the historical severity is actually unknown, choosing info would invent knowledge. A nullable field or an explicit unknown category may be a more truthful model for that different situation.

The supported alterations and restrictions differ between engines and versions. Check dependent application queries and inserts as well as the table schema. Code that assumes a particular column count or inserts values without a column list can break after an otherwise valid schema change.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Choose a historically truthful default, if any.
• Use explicit column lists in inserts.
• Check readers and writers that rely on the old schema shape.