Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is CHECK constraint same as TRIGGER? Expand / Collapse
Author
Message
Posted Friday, August 7, 2009 12:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 2:55 AM
Points: 1,101, Visits: 5,280
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?
Post #766706
Posted Friday, August 7, 2009 1:18 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:52 PM
Points: 530, Visits: 1,134

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
Post #766723
Posted Friday, August 7, 2009 2:47 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 2:55 AM
Points: 1,101, Visits: 5,280
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)

Post #766757
Posted Friday, August 7, 2009 12:47 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 4:51 PM
Points: 11,264, Visits: 13,023
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

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
Post #767223
Posted Saturday, August 8, 2009 1:20 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 13, 2014 10:40 AM
Points: 368, Visits: 543
I completly agree to Jack.


Regards,
Sarabpreet Singh
SQLServerGeeks.com/blogs/sarab
www.Sarabpreet.com
Twitter: @Sarab_SQLGeek
Post #767435
Posted Saturday, August 8, 2009 4:16 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #767447
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse