SQL DEFAULT Constraint

SQL DEFAULT Constraint

DEFAULT constraint is used to insert default values into columns.

If no other value is specified, the default value will be added to all new records.

SQL DEFAULT Constraint on CREATE TABLE

The following SQL creates a DEFAULT constraint for the 'City' column when the 'Persons' table is created:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)

DEFAULT constraints can also be used to insert system values by using functions like GETDATE():

CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
OrderDate date DEFAULT GETDATE()
)

SQL DEFAULT Constraint on ALTER TABLE

If you want to create a DEFAULT constraint for the 'City' column when the table already exists, please use the following SQL:

MySQL:

ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'

Remove DEFAULT Constraint

To remove the DEFAULT constraint, please use the following SQL:

MySQL:

ALTER TABLE Persons
ALTER City DROP DEFAULT

SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT