Check Constraint

Check:

Check limits the values that are accepted by one or more columns.

The Below code accepts only values A or k or R or S, it will not accept other than this.

USE Rohan

GO

CREATE TABLE CHEECK (ID INT, SHORT VARCHAR(2) CHECK (SHORT LIKE '[A,K,R,S]'))

What happens if we insert other than this?

INSERT INTO CHEECK(ID, SHORT) VALUES(1,'N')

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "CK__CHEECK__SHORT__607251E5". The conflict occurred in database "Rohan", table "dbo.CHEECK", column 'SHORT'.

The statement has been terminated.

LIMITATION OF CHECK:

USE Rohan

GO

CREATE TABLE CHEECK(ID INT,SHORT VARCHAR(2) CHECK(SHORT LIKE '[A,K,R,S]'))

INSERT INTO CHEECK(ID) VALUES(1)

SELECT * FROM CHEECK

ID     SHORT

1      NULL

If there is no value the column accepts Null Values, even though there is Check Constraint.

Check Constraints works at the row level, checks whether the row is true, if there is no row for check column it will take default NULL values.

Example:

USE Rohan

GO

CREATE TABLE CHEECK (col1 int, col2 int);

GO

CREATE FUNCTION FOREXAMPLE()

RETURNS int

AS

BEGIN

   DECLARE @COUNT int

   SELECT @COUNT = COUNT(*) FROM CHEECK

   RETURN @COUNT

END;

GO

ALTER TABLE CHEECK

ADD CONSTRAINT chkRowCount CHECK (dbo.FOREXAMPLE() >= 1 );

GO

The check constraint says the row count should be greater than 1 ,

Check constraint does not work for DELETE Statements

INSERT INTO CHEECK VALUES (10, 10)

GO

DELETE CHEECK WHERE col1 = 10;

SELECT * from CHEECK

ONLY EMPTY TABLE RETURNS, Even though  the check constraint says the count of the table should be greater than 1.