SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is CHECK constraint same as TRIGGER?


Is CHECK constraint same as TRIGGER?

Author
Message
Suresh B.
Suresh B.
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2548 Visits: 5326
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
(
id int identity(1, 1),
data char(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?
Chand00
Chand00
SSC Eights!
SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)SSC Eights! (926 reputation)

Group: General Forum Members
Points: 926 Visits: 1257

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

Thanks
Chandra Mohan

Suresh B.
Suresh B.
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2548 Visits: 5326
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
  Jack Corbett
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24480 Visits: 14905
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Sarab_SQLGeek
Sarab_SQLGeek
Say Hey Kid
Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)

Group: General Forum Members
Points: 692 Visits: 558
I completly agree to Jack.

Regards,
Sarabpreet Singh Cool
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7255 Visits: 4639
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search