SQL DEFAULT Constraint

SQL DEFAULT Constraint provides a default value to a column when adding new record and no value provided for a column.

Here is an example:  OrderDate Column has default value  GetDate() SQL function. If user doesn’t provide value for OrderDate, by default it will take today date in case of blank

Syntax: Create DEFAULT Constraint on Create Table:

The below SQL script creates a Default constraint for OrderDate Column

CREATE TABLE Orders (

    OrderID int NOT NULL,

    OrderNumber int NOT NULL,

    StatusID int, 

    OrderAmount Decimal(10,2),

    OrderDate date DEFAULT GETDATE(),

    PRIMARY KEY (OrderID),

    CONSTRAINT FK_OrderOrderStatus FOREIGN KEY (StatusID)

    REFERENCES OrderStatus(StatusID)

);

Syntax: Create DEFAULT Constraint on Alter Table:

The below SQL script creates a Default constraint for OrderDate Column of a table, which is already created

ALTER TABLE dbo.Orders

ADD CONSTRAINT df_OrderDate

DEFAULT GETDATE() FOR OrderDate;

Syntax: Drop DEFAULT Constraint:

The below SQL script uses to drop a Default constraint from table

ALTER TABLE Orders

DROP CONSTRAINT df_OrderDate;