SQL DEFAULT Constraint

Submitted by admin on Monday, April 25, 2011 - 11:11.

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