SQL DEFAULT Constraint
SQL DEFAULT Constraint
DEFAULT constraint is used to insert the default value to the column.
If no other value is specified, then the default value will be added to all new records.
SQL DEFAULT constraint CREATE TABLE when
The following SQL when the "Persons" table is created to create a DEFAULT constraint on the "City" columns:
My SQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)
By using such a function similar GETDATE (), DEFAULT constraint can also be used to insert system values:
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
)
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
)
SQL DEFAULT constraint ALTER TABLE when
When the table has been created, create a DEFAULT constraint for a column in the "City", please use the following SQL:
MySQL:
ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'
ALTER City SET DEFAULT 'SANDNES'
SQL Server / MS Access:
ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'
ALTER COLUMN City SET DEFAULT 'SANDNES'
Oracle:
ALTER TABLE Persons
MODIFY City DEFAULT 'SANDNES'
MODIFY City DEFAULT 'SANDNES'
Undo DEFAULT constraint
To drop a DEFAULT constraint, use the following SQL:
MySQL:
ALTER TABLE Persons
ALTER City DROP DEFAULT
ALTER City DROP DEFAULT
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT
ALTER COLUMN City DROP DEFAULT