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:
(
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:
(
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 City SET DEFAULT 'SANDNES'
SQL Server / MS Access:
ALTER COLUMN City SET DEFAULT 'SANDNES'
Oracle:
MODIFY City DEFAULT 'SANDNES'
Undo DEFAULT constraint
To drop a DEFAULT constraint, use the following SQL:
MySQL:
ALTER City DROP DEFAULT
SQL Server / Oracle / MS Access:
ALTER COLUMN City DROP DEFAULT