Skip to main content

SQL DEFAULT Constraint

Body

The DEFAULT Constraint is used to insert the default values into a column during design time.

The default values is used in new record if no value is specified during INSERT Statement.

The default

SQL DEFAULT Constraint Syntax

CREATE TABLE table_name ( column_name1 data_type DEFAULT Value, column_name2 data_type DEFAULT Value, column_name3 data_type DEFAULT Value, .... )

Example

CREATE TABLE Users ( ID text NOT NULL, Firstname text, Lastname text, Salary float, DeptID int NOT NULL, DateAdded date DEFAULT GetDate() )

Users

ID Firstname Lastname Salary DeptID DateAdded

This will set the default value into DateAdded field if no value is passed using the INSERT Statement and will give the default system date.

ALTER TABLE method

Example MySQL

ALTER TABLE Users ALTER DateAdded SET DEFAULT GetDate()

Example Oracle/SQL server/MS Access

ALTER TABLE Users ALTER COLUMN DateAdded SET DEFAULT GetDate()

TO REMOVE DEFAULT CONSTRAINT

Example MySQL

ALTER TABLE Users ALTER DateAdded DROP DEFAULT

Example Oracle/SQL Server/MS Access

ALTER TABLE Users ALTER COLUMN DateAdded DROP DEFAULT

Add new comment