SQL Default

SQL DEFAULT Constraint

A column’s default value can be set using the DEFAULT constraint.

If no alternative value is supplied, the default value will be appended to all newly created records.

When the “Persons” table is created, the following SQL creates a DEFAULT value for the “City” column:

System values can also be inserted using the DEFAULT constraint and functions such as GETDATE():

				
					CREATE TABLE Orders (
    ID int NOT NULL,
    OrderNumber int NOT NULL,
    OrderDate date DEFAULT GETDATE()
);

When the table has already been established, use the following SQL to create a DEFAULT constraint on the “City” column:

				
					ALTER TABLE Persons
ALTER City SET DEFAULT 'Sandnes';

				
					ALTER TABLE Persons
ADD CONSTRAINT df_City
DEFAULT 'Sandnes' FOR City;

				
					ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'Sandnes';

				
					ALTER TABLE Persons
MODIFY City DEFAULT 'Sandnes';

A DEFAULT constraint can be removed by using the following SQL:

				
					ALTER TABLE Persons
ALTER City DROP DEFAULT;

				
					ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;