sql check constraint

SQL Advance

homeBack

Next

SQL CHECK Constraint

  • SQL UNIQUE Constraint

The CHECK constraint is used to limit the value range that can be placed in a column. 
If you define a CHECK constraint on a single column it allows only certain values for this column. If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

  • SQL CHECK Constraint on CREATE TABLE

The following SQL creates a CHECK constraint on the “S.no” column when the “emp” table is created. The CHECK constraint specifies that the column “S.no” must only include integers greater than 0.

MySQL:

CREATE TABLE emp
(
S.no int NOT NULL,
Name2 varchar(255) NOT NULL,
Name1 varchar(255),
City varchar(255),
CHECK (S.no>0)
)

SQL Server / Oracle / MS Access:

CREATE TABLE emp
(
S.no int NOT NULL CHECK (S.no>0),
Name2 varchar(255) NOT NULL,
Name1 varchar(255),
City varchar(255)
)

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE emp
(
S.no int NOT NULL,
Name2 varchar(255) NOT NULL,
Name1 varchar(255),
City varchar(255),
CONSTRAINT chk_emp CHECK (S.no>0 AND City=’Hyderabad’)
)

  • SQL CHECK Constraint on ALTER TABLE

To create a CHECK constraint on the “S.no” column when the table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE emp
ADD CHECK (S.no>0)

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE emp
ADD CONSTRAINT chk_emp CHECK (S.no>0 AND City=’Hyderabad’)

  • To DROP a CHECK Constraint

To drop a CHECK constraint, use the following SQL:

SQL Server / Oracle / MS Access:

ALTER TABLE emp
DROP CONSTRAINT chk_emp

MySQL:

ALTER TABLE emp
DROP CHECK chk_emp

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s