SQL Default

Filling in values automatically

A DEFAULT constraint gives a column a fallback value that the database uses when a new row is inserted without specifying that column. This saves the application from repeating the same value on every insert and ensures a sensible, consistent value is always present. Common uses include a status of 'active', a count that starts at zero, or a timestamp recording when the row was created.

Defining DEFAULT at table creation

CREATE TABLE Tickets (
    TicketID   INT NOT NULL PRIMARY KEY,
    Subject    VARCHAR(200) NOT NULL,
    Status     VARCHAR(20) DEFAULT 'open',
    Priority   INT DEFAULT 3,
    CreatedAt  DATE DEFAULT CURRENT_DATE
);

Status defaults to 'open', Priority defaults to 3, and CreatedAt defaults to today's date. Any insert that omits these columns will receive the preset values.

-- Only Subject is given; the rest use their defaults
INSERT INTO Tickets (TicketID, Subject)
VALUES (1, 'Cannot log in');

-- An explicit value overrides the default
INSERT INTO Tickets (TicketID, Subject, Priority)
VALUES (2, 'Payment failed', 1);

• A default can be a literal value such as a number or a string.
• It can also be a function like CURRENT_DATE or CURRENT_TIMESTAMP.
• Passing an explicit value in the INSERT always overrides the default.
• A default is used only when the column is left out of the insert entirely.

Adding or removing DEFAULT later

-- SQL Server: add a named default
ALTER TABLE Tickets
ADD CONSTRAINT df_status DEFAULT 'open' FOR Status;

-- MySQL / PostgreSQL: set and drop a default
ALTER TABLE Tickets ALTER COLUMN Status SET DEFAULT 'open';
ALTER TABLE Tickets ALTER COLUMN Status DROP DEFAULT;