Friday, February 15, 2013

How to define CHECK Constraint on a Table in SQL Server

SQL Server CHECK Constraint
The CHECK constraint is used to validate and controls the data that we are entering into a column, on which the constraint is defined.
If you define a CHECK constraint on a single column it validate and controls only values for that specific column.
If you define a CHECK constraint on multiple columns then it will limit the values in certain columns based on values in other columns in the row.

Creating CHECK Constraint with CREATE TABLE Statement

The following SQL creates a CHECK constraint on the "C_Id" column when the "Customers" table is created. The CHECK constraint specifies that the column "C_Id" must only include integers greater than 0.
SQL Server / Oracle / MS Access:
CREATE TABLE Customers
(
C_Id int NOT NULL CHECK (C_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

MySQL:

CREATE TABLE Customers
(
C_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (C_Id>0)
)

Creating a CHECK constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Customers
(
C_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Customer CHECK (C_Id>0 AND City='Sandnes')
)

Creating a CHECK Constraint with ALTER TABLE Statement:

To create a CHECK constraint on the "C_Id" column when the table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Customers ADD CHECK (C_Id>0)

To define a CHECK constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Customers ADD CONSTRAINT Chk_Customer 
CHECK (C_Id>0 AND City='Sandnes')

To Drop a CHECK Constraint, use the following SQL Statements:
SQL SERVER / ORACLE / MS ACCESS:
ALTER TABLE Customers DROP CONSTRAINT chk_Customer

MySQL:
ALTER TABLE Customers DROP CHECK chk_Customer

Thanks,Tamatam

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog