Is CHECK constraint same as TRIGGER?

Question

Is CHECK constraint same as TRIGGER?

Suresh B.

SSC-Insane

Points: 22986
More actions
August 7, 2009 at 12:17 am

#140579

I just noticed that CHECK constraint behaves like after TRIGGER. That is, the row is inserted then checked whehter it satisfies the condition. If not, rolled back.
See the example below. Notice the missing id (identity) values 2, 4, and 6.
create table CheckTest
(
idint identity(1, 1),
datachar(1) check (Data not in ('X', 'Y', 'Z'))
)
go
insert into CheckTest(data) values ('A')
insert into CheckTest(data) values ('X')
insert into CheckTest(data) values ('B')
insert into CheckTest(data) values ('Y')
insert into CheckTest(data) values ('C')
insert into CheckTest(data) values ('Z')
insert into CheckTest(data) values ('D')
select id, Data from CheckTest
1 A
3 B
5 C
7 D
My question is, why checking is not done before inserting?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Chand00 SSCertifiable Points: 6466 More actions · Answer 1

I just noticed that CHECK constraint behaves like after TRIGGER. That is, the row is inserted then checked whehter it satisfies the condition. If not, rolled back.

Suresh,

No. CHECK constraint checks before insertion of data. When you are trying to insert value 'X' into the table CHECK constraint fails the insert statement. As you have a identity column here even though your insert statement failed the identity value gets increased. Its the defaul behaviour of an identity column.

Try this example:

create table identTest

(

id int identity(1, 1),

data char(1)

)

go

insert into identTest(data) values ('A')

insert into identTest(data) values ('XX)

insert into identTest(data) values ('BV')

insert into identTest(data) values ('Y')

insert into identTest(data) values ('C')

insert into identTest(data) values ('ZX')

insert into identTest(data) values ('D')

insert into identTest(data) values ('DARK')

go

select * from identTest order by id

go

[font="Verdana"]Thanks
Chandra Mohan[/font]

Suresh B. SSC-Insane Points: 22986 More actions · Answer 2

Hi Chandu,

I think CHECK constraint checks AFTER insertion of data. If we do row count in the ckeck constraint it will have the new row. See the following example:

CREATE TABLE CheckTbl (col1 int, col2 int);

GO

CREATE FUNCTION CheckFnctn()

RETURNS int

AS

BEGIN

DECLARE @retval int

SELECT @retval = COUNT(*) FROM CheckTbl

RETURN @retval

END;

GO

ALTER TABLE CheckTbl

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

GO

insert into CheckTbl values (1, 1)

(1 row(s) affected)

Jack Corbett SSC Guru Points: 184393 More actions · Answer 3

Did you read the BOL entry that contained the example you posted? It explicitly states (emphasis is mine):

CHECK constraints reject values that evaluate to FALSE. Because null values evaluate to UNKNOWN, their presence in expressions may override a constraint. For example, suppose you place a constraint on an int column MyColumn specifying that MyColumn can contain only the value 10 (MyColumn = 10). If you insert the value NULL into MyColumn, the Database Engine inserts NULL and does not return an error.
A CHECK constraint returns TRUE when the condition it is checking is not FALSE for any row in the table. If a table that has just been created does not have any rows, any CHECK constraint on this table is considered valid. This situation can produce unexpected results, as in the following example.
CREATE TABLE CheckTbl (col1 int, col2 int);
GO
CREATE FUNCTION CheckFnctn()
RETURNS int
AS
BEGIN
DECLARE @retval int
SELECT @retval = COUNT(*) FROM CheckTbl
RETURN @retval
END;
GO
ALTER TABLE CheckTbl
ADD CONSTRAINT chkRowCount CHECK (dbo.CheckFnctn() >= 1 );
GO
The CHECK constraint being added specifies that there must be at least one row in table CheckTbl. However, because there are no rows in the table against which to check the condition of this constraint, the ALTER TABLE statement succeeds.

So this is not a valid check. If you change the value being checked to anything greater than 1 then a single row insert fails.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Sarab_SQLChamp Hall of Fame Points: 3882 More actions · Answer 4

I completly agree to Jack.

Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 5

Just for documentation purposes and to clarify the missleading title that reads "Is CHECK constraint same as TRIGGER?" ... no, check constraint and triggers are not the same.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.