Tuesday, July 30, 2019

SQL Check Constraint

A SQL Check Constraint is defined a condition on a column of table that must satisfy then it allows to insert or update for a column.

Here is an example: OrderAmount must be positive value

Syntax: Create Check Constraint on Create Table:

The below SQL script a Check Constraint on OrderAmount column and it will allow only positive value for OrderAmount.

   OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    StatusID int, 
    OrderAmount Decimal(10,2) Check(OrderAmount > 0) NOT NULL
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_OrderOrderStatus FOREIGN KEY (StatusID)
    REFERENCES OrderStatus(StatusID)

Basically Check Keyword is used to apply Check Constraint on a column and usually it is a logically expression

Check(OrderAmount > 0) NOT NULL

Syntax: Create Check Constraint on Alter Table:

The below SQL script a Check Constraint on table, which is already created

ALTER TABLE dbo.Orders
ADD CONSTRAINT OrderAmount_Ckeck Check(OrderAmount > 0);

Syntax: Drop Check Constraint:

The below SQL is used to drop the check constraint from Table

DROP CONSTRAINT OrderAmount_Ckeck;

No comments:

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...