Friday, 15 February 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

1 comment:

Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts