Check Constraint UDF: Update vs. Insert

I am using a check constraint on one of my tables. It appears to me
that the constraint processes an insert AFTER the row is inserted on
an insert, but BEFORE the data is updated on an update. here's my
example:
Table:
CREATE TABLE [test1] (
[test] [int] NOT NULL )
Function:
CREATE FUNCTION dbo.fn_RI_Test (@.test int)
RETURNS INT
AS
BEGIN
declare @.cnt int
select @.cnt = count(*) from test1 where test=@.test
return @.cnt
END
here's my constraint added to the table test1:
alter table test1 add constraint ck_test1 check
(dbo.fn_RI_test([test])=1)
Now, if I insert new rows:
insert into test1 values (1) - works fine
insert into test1 values (2) - works fine
insert into test1 values (1) - error:
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
The conflict occurred in database 'JHRA_Test', table 'test1', column
'test'.
The statement has been terminated.
BUT, if I perform an update:
update test1 set test=1 where test=2
IT WORKS and now there are 2 rows with test=1
Am I crazy? Is there any way for me to know in the function whether
this is an update or an insert? Any other options? BTW, I don't need
to make a single column unique in this manner, my needs are much more
complex. this is just an illustration which demonstrates my issue.
Thanks!rich,
Can you tell us more about your needs?
This is a known behavior with a udf referencing same table in a constraint
and It has been fixed in 2005 version.
AMB
"rich" wrote:
> I am using a check constraint on one of my tables. It appears to me
> that the constraint processes an insert AFTER the row is inserted on
> an insert, but BEFORE the data is updated on an update. here's my
> example:
> Table:
> CREATE TABLE [test1] (
> [test] [int] NOT NULL )
> Function:
> CREATE FUNCTION dbo.fn_RI_Test (@.test int)
> RETURNS INT
> AS
> BEGIN
> declare @.cnt int
> select @.cnt = count(*) from test1 where test=@.test
> return @.cnt
> END
> here's my constraint added to the table test1:
> alter table test1 add constraint ck_test1 check
> (dbo.fn_RI_test([test])=1)
> Now, if I insert new rows:
> insert into test1 values (1) - works fine
> insert into test1 values (2) - works fine
> insert into test1 values (1) - error:
> Server: Msg 547, Level 16, State 1, Line 1
> INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
> The conflict occurred in database 'JHRA_Test', table 'test1', column
> 'test'.
> The statement has been terminated.
> BUT, if I perform an update:
> update test1 set test=1 where test=2
> IT WORKS and now there are 2 rows with test=1
> Am I crazy? Is there any way for me to know in the function whether
> this is an update or an insert? Any other options? BTW, I don't need
> to make a single column unique in this manner, my needs are much more
> complex. this is just an illustration which demonstrates my issue.
> Thanks!
>|||Alejandro, thanks for the reply - I was trying to find some resource
to confirm or deny the workings so I appreciate it.
I will share the true source of my problem in case you, or others,
would like to weigh in on the best method.
Basically, all of our data tables have a delete_flag bit field.
At the same time, I am trying to disallow duplicate combination of 3
fields. (sounds like a primary key, huh?)
Each table has an Identity field as the primary key.
I can't create a unique index on the three field combo because I allow
logical deletes using the delete_flag field.
I tried including the delete_flag in a four field index, but then you
can only delete each unique combination once.
I need unlimited logically deleted rows where my three fields are not
necessarily unique,
but only one non-deleted one row for each unique combination.
make sense?
On Feb 22, 3:26 pm, Alejandro Mesa
<AlejandroM...@.discussions.microsoft.com> wrote:
> rich,
> Can you tell us more about your needs?
> This is a known behavior with a udf referencing same table in a constraint
> and It has been fixed in 2005 version.
> AMB
>
> "rich" wrote:
> > I am using a check constraint on one of my tables. It appears to me
> > that the constraint processes an insert AFTER the row is inserted on
> > an insert, but BEFORE the data is updated on an update. here's my
> > example:
> > Table:
> > CREATE TABLE [test1] (
> > [test] [int] NOT NULL )
> > Function:
> > CREATE FUNCTION dbo.fn_RI_Test (@.test int)
> > RETURNS INT
> > AS
> > BEGIN
> > declare @.cnt int
> > select @.cnt = count(*) from test1 where test=@.test
> > return @.cnt
> > END
> > here's my constraint added to the table test1:
> > alter table test1 add constraint ck_test1 check
> > (dbo.fn_RI_test([test])=1)
> > Now, if I insert new rows:
> > insert into test1 values (1) - works fine
> > insert into test1 values (2) - works fine
> > insert into test1 values (1) - error:
> > Server: Msg 547, Level 16, State 1, Line 1
> > INSERT statement conflicted with COLUMN CHECK constraint 'ck_test1'.
> > The conflict occurred in database 'JHRA_Test', table 'test1', column
> > 'test'.
> > The statement has been terminated.
> > BUT, if I perform an update:
> > update test1 set test=1 where test=2
> > IT WORKS and now there are 2 rows with test=1
> > Am I crazy? Is there any way for me to know in the function whether
> > this is an update or an insert? Any other options? BTW, I don't need
> > to make a single column unique in this manner, my needs are much more
> > complex. this is just an illustration which demonstrates my issue.
> > Thanks!- Hide quoted text -
> - Show quoted text -|||rich wrote:
> Alejandro, thanks for the reply - I was trying to find some resource
> to confirm or deny the workings so I appreciate it.
> I will share the true source of my problem in case you, or others,
> would like to weigh in on the best method.
> Basically, all of our data tables have a delete_flag bit field.
> At the same time, I am trying to disallow duplicate combination of 3
> fields. (sounds like a primary key, huh?)
> Each table has an Identity field as the primary key.
> I can't create a unique index on the three field combo because I allow
> logical deletes using the delete_flag field.
> I tried including the delete_flag in a four field index, but then you
> can only delete each unique combination once.
> I need unlimited logically deleted rows where my three fields are not
> necessarily unique,
> but only one non-deleted one row for each unique combination.
> make sense?
The solution(s) to the problem "unique constraint with multiple NULLs"
also applies to your case, only in your case the delete flag acts as a
NULL.
So for example, something like the script below could be a solution:
ALTER TABLE YourTable
ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
ALTER TABLE YourTable
ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
HTH,
Gert-Jan|||Gert-Jan, thanks for the tip!
I get it and it makes sense.
I also just tried a trigger (based on the above "test1" table) and it
works too.
Not sure what I'd rather do - add a new trigger to a bunch of tables
or a column and constraint.
here's the trigger in case anyone is interested:
CREATE TRIGGER dbo.tr_Test
ON [dbo].[test1]
FOR INSERT, UPDATE
AS
declare @.cnt int
BEGIN
select @.cnt=count(*) from test1 where test in (select test from
inserted)
if @.cnt>1 rollback tran --it's always 1 because the newly inserted or
updated row is included in the test1 table
END
Thanks for everyone's help!
On Feb 22, 4:01 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> rich wrote:
> > Alejandro, thanks for the reply - I was trying to find some resource
> > to confirm or deny the workings so I appreciate it.
> > I will share the true source of my problem in case you, or others,
> > would like to weigh in on the best method.
> > Basically, all of our data tables have a delete_flag bit field.
> > At the same time, I am trying to disallow duplicate combination of 3
> > fields. (sounds like a primary key, huh?)
> > Each table has an Identity field as the primary key.
> > I can't create a unique index on the three field combo because I allow
> > logical deletes using the delete_flag field.
> > I tried including the delete_flag in a four field index, but then you
> > can only delete each unique combination once.
> > I need unlimited logically deleted rows where my three fields are not
> > necessarily unique,
> > but only one non-deleted one row for each unique combination.
> > make sense?
> The solution(s) to the problem "unique constraint with multiple NULLs"
> also applies to your case, only in your case the delete flag acts as a
> NULL.
> So for example, something like the script below could be a solution:
> ALTER TABLE YourTable
> ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
> ALTER TABLE YourTable
> ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
> HTH,
> Gert-Jan- Hide quoted text -
> - Show quoted text -|||On Feb 22, 2:26 pm, Alejandro Mesa
<AlejandroM...@.discussions.microsoft.com> wrote:
> This is a known behavior with a udf referencing same table in aconstraint
> and It has been fixed in 2005 version.
> AMB
>
I'm having trouble with this as well. One of our tables stores SSN
which should be unique but may also be null if the SSN isn't available
when entering the person's information. So I put a check constraint on
my column using a UDF that checks to see if any records already exist
with that SSN. I'm getting errors inserting data, because it inserts
the record before the check constraint fires.
This apparently hasn't been fixed in 2005, because that is what
version of SQL server I'm using. and my database is at that
compatibility level.
Is there a way to do this with a check constraint or do I have to use
a trigger?
Thanks.
-- Rayne|||Well, for one thing, using a constraint will avoid unnecessary page
splits, because it will prevent duplicate rows. A trigger will roll back
duplicate rows.
Also, I think that in this case, writing a trigger is more error prone
than defining the extra column and unique constraint.
Of course, the unique index will use slightly more space. But then
again, you also benefit from the fact that there now is a highly
selective index on the natural key.
So in this case, IMO the constraint wins from the trigger hands down...
Gert-Jan
rich wrote:
> Gert-Jan, thanks for the tip!
> I get it and it makes sense.
> I also just tried a trigger (based on the above "test1" table) and it
> works too.
> Not sure what I'd rather do - add a new trigger to a bunch of tables
> or a column and constraint.
> here's the trigger in case anyone is interested:
> CREATE TRIGGER dbo.tr_Test
> ON [dbo].[test1]
> FOR INSERT, UPDATE
> AS
> declare @.cnt int
> BEGIN
> select @.cnt=count(*) from test1 where test in (select test from
> inserted)
> if @.cnt>1 rollback tran --it's always 1 because the newly inserted or
> updated row is included in the test1 table
> END
> Thanks for everyone's help!
> On Feb 22, 4:01 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> > rich wrote:
> >
> > > Alejandro, thanks for the reply - I was trying to find some resource
> > > to confirm or deny the workings so I appreciate it.
> >
> > > I will share the true source of my problem in case you, or others,
> > > would like to weigh in on the best method.
> >
> > > Basically, all of our data tables have a delete_flag bit field.
> > > At the same time, I am trying to disallow duplicate combination of 3
> > > fields. (sounds like a primary key, huh?)
> > > Each table has an Identity field as the primary key.
> > > I can't create a unique index on the three field combo because I allow
> > > logical deletes using the delete_flag field.
> > > I tried including the delete_flag in a four field index, but then you
> > > can only delete each unique combination once.
> >
> > > I need unlimited logically deleted rows where my three fields are not
> > > necessarily unique,
> > > but only one non-deleted one row for each unique combination.
> >
> > > make sense?
> >
> > The solution(s) to the problem "unique constraint with multiple NULLs"
> > also applies to your case, only in your case the delete flag acts as a
> > NULL.
> >
> > So for example, something like the script below could be a solution:
> >
> > ALTER TABLE YourTable
> > ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
> >
> > ALTER TABLE YourTable
> > ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
> >
> > HTH,
> > Gert-Jan- Hide quoted text -
> >
> > - Show quoted text -|||Thanks again Gert-Jan
Rayne, you may or may not have seen the same issue.
You state
"I'm getting errors inserting data, because it inserts
the record before the check constraint fires. "
but it does that in SQL 2000 as well and thus your comparison in the
constraint has to be >1 as opposed to >0.
In other words, you have to assume that the newly inserted row is
included in the result query.
The problem I was having is that the above is NOT TRUE for updates. So
perhaps you could update your constraint and test it for inserts and
updates to see if it works as expected.
On Feb 22, 6:50 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> Well, for one thing, using a constraint will avoid unnecessary page
> splits, because it will prevent duplicate rows. A trigger will roll back
> duplicate rows.
> Also, I think that in this case, writing a trigger is more error prone
> than defining the extra column and unique constraint.
> Of course, the unique index will use slightly more space. But then
> again, you also benefit from the fact that there now is a highly
> selective index on the natural key.
> So in this case, IMO the constraint wins from the trigger hands down...
> Gert-Jan
>
> rich wrote:
> > Gert-Jan, thanks for the tip!
> > I get it and it makes sense.
> > I also just tried a trigger (based on the above "test1" table) and it
> > works too.
> > Not sure what I'd rather do - add a new trigger to a bunch of tables
> > or a column and constraint.
> > here's the trigger in case anyone is interested:
> > CREATE TRIGGER dbo.tr_Test
> > ON [dbo].[test1]
> > FOR INSERT, UPDATE
> > AS
> > declare @.cnt int
> > BEGIN
> > select @.cnt=count(*) from test1 where test in (select test from
> > inserted)
> > if @.cnt>1 rollback tran --it's always 1 because the newly inserted or
> > updated row is included in the test1 table
> > END
> > Thanks for everyone's help!
> > On Feb 22, 4:01 pm, Gert-Jan Strik <s...@.toomuchspamalready.nl> wrote:
> > > rich wrote:
> > > > Alejandro, thanks for the reply - I was trying to find some resource
> > > > to confirm or deny the workings so I appreciate it.
> > > > I will share the true source of my problem in case you, or others,
> > > > would like to weigh in on the best method.
> > > > Basically, all of our data tables have a delete_flag bit field.
> > > > At the same time, I am trying to disallow duplicate combination of 3
> > > > fields. (sounds like a primary key, huh?)
> > > > Each table has an Identity field as the primary key.
> > > > I can't create a unique index on the three field combo because I allow
> > > > logical deletes using the delete_flag field.
> > > > I tried including the delete_flag in a four field index, but then you
> > > > can only delete each unique combination once.
> > > > I need unlimited logically deleted rows where my three fields are not
> > > > necessarily unique,
> > > > but only one non-deleted one row for each unique combination.
> > > > make sense?
> > > The solution(s) to the problem "unique constraint with multiple NULLs"
> > > also applies to your case, only in your case the delete flag acts as a
> > > NULL.
> > > So for example, something like the script below could be a solution:
> > > ALTER TABLE YourTable
> > > ADD ExtraColumn AS CASE WHEN delete_flag = 1 THEN IdentityColumn END
> > > ALTER TABLE YourTable
> > > ADD CONSTRAINT YourUnique UNIQUE (UniqueColumn, ExtraColumn)
> > > HTH,
> > > Gert-Jan- Hide quoted text -
> > > - Show quoted text -- Hide quoted text -
> - Show quoted text -|||On Feb 23, 9:24 am, "rich" <rwal...@.integratec.biz> wrote:
> Rayne, you may or may not have seen the same issue.
> You state
> "I'm getting errors inserting data, because it inserts
> the record before the check constraint fires. "
> but it does that in SQL 2000 as well and thus your comparison in the
> constraint has to be >1 as opposed to >0.
> In other words, you have to assume that the newly inserted row is
> included in the result query.
I realized this after the fact. My constraint was checking only to see
if a record already existed with that field value...not counting how
many. So when it did the check, of course it existed since it had
inserted the record first.
I created a trigger for insert, update that checks the count instead
of just existance and it's working correctly now.