Tuesday, July 30, 2019

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;

1 comment:

webhutWebsiteDesign said...

Nice post. I read your blog it’s very nice and very helpful, I learn something new in every time from this website Thanks for sharing.
Click here to know more "software development company ahmedabad"

SQL Server - Identify unused indexes

 In this blog, we learn about the index usage information (SYS.DM_DB_INDEX_USAGE_STATS) and analyze the index usage data (USER_SEEKS, USER_S...