SQL DEFAULT
SQL DEFAULT constraint
DEFAULT constraint is used to insert a default value into a column.
If no other value is specified, the default value will be added to all new records.
SQL DEFAULT constraint when CREATE TABLE
The following SQL creates a DEFAULT constraint on the "City" column when the "Persons" table is created:
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 something like GETDATE() like this function, DEFAULT constraints 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 when ALTER TABLEWhen the table has been created, such as To create a DEFAULT constraint on the "City" column, please use the following SQL:
MySQL:
ALTER TABLE Persons
ALTER City SET DEFAULT ' SANDNES'
SQL Server/MS Access:
ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'
Oracle:
ALTER TABLE Persons
MODIFY City DEFAULT 'SANDNES'
Withdraw DEFAULT constraintTo revoke 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
