SQL DEFAULT constraints

SQL DEFAULT constraints

DEFAULT constraints are used to insert default values into columns.

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

SQL DEFAULT constraints at CREATE TABLE

The following SQL creates default constraints on the City column when the People 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'
)

Default constraints can also be used to insert system values by using functions such as GETDATE():

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
)

SQL DEFAULT constraints at ALTER TABLE

When a table has been created, to create a DEFAULT constraint in the City column, use the following SQL:

Mysql:

ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'

SQL Server / MS Access：

ALTER TABLE Persons 
ADD CONSTRAINT DF_Persons_City DEFAULT('SANDNES') FOR City

--注释
--Persons 为表名
--City 为列名
--DF_Persons_City 为我们创建的默认约束的名称 约束名称一般为:约束类型简称_表名_列名

Oracle：

ALTER TABLE Persons
MODIFY City DEFAULT 'SANDNES'

Undo the DEFAULT constraint

To undo the DEFAULT constraint, 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