How to use DEFAULT Constraint in SQL Server

SQL Server DEFAULT Constraint

The DEFAULT constraint is used to insert a default value into a column.The default value will be added to all new records, if no other value is specified.

Creating DEFAULT Constraint with CREATE TABLE Statement

The following SQL creates a DEFAULT constraint on the "City" column when the "Customers" table is created:

My SQL / SQL Server / Oracle / MS Access:

CREATE TABLE Customers

(

C_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

City varchar(255) DEFAULT 'Hyderabad'

Gender varchar(50)

)

INSERT Statement for Table with DEFAULT Constraint values

When a Field is declared as DEFAULT , it will take the default value specified , we no need to insert this value in the INSERT INTO statement.So we have to ignore or skip it.This we can do as follows :

INSERT INTO Customers values (123,'Tamatam','Reddy',DEFAULT,'Male')

Here , In the INSERT INTO statement we passed DEFAULT as a value for the DEFAULT Value City.,so that it will take the Default value specified(Hyderabad) in the Table Creation.

The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE():

CREATE TABLE Orders

(

O_Id int NOT NULL,

OrderNo int NOT NULL,

C_Id int,

OrderDate Date DEFAULT GETDATE()

)

Creating SQL DEFAULT Constraint with ALTER TABLE Statement

To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL:

SQL Server / MS Access:

ALTER TABLE Customers ALTER COLUMN City SET DEFAULT 'Hyderabad'

MySQL:

ALTER TABLE Customers ALTER City SET DEFAULT 'Hyderabad'

Oracle:

ALTER TABLE Customers MODIFY City DEFAULT 'Hyderabad'

To drop a DEFAULT constraint
SQL Server / Oracle / MS Access:

ALTER TABLE Customers ALTER COLUMN City DROP DEFAULT

MySQL:

ALTER TABLE Customers ALTER City DROP DEFAULT

--------------------------------------------------------------------------------------------------------