CODEWITHSUNDEEP
sqlsql-serverdatabasefunction
iBrahim
iBrahim

Reputation: 43

function do not work in CHECK Constraint on sql server

I have problem on my Database, I try simplify my database.. after a lot of tries the function use 'X' table , then use the function on 'X' table.you can not use function use same table...

--after create table:

create table Items(
ID int,
Price money
);

--Insert some values

insert into Items Values(1,4);
insert into Items Values(2,5);

--create a function use above table

CREATE FUNCTION GetMaxPrice
(
    -- Add the parameters for the function here

)
RETURNS Money
AS
BEGIN
    -- Declare the return variable here
    DECLARE @result money = 5;

    select @result = max(Price)
    from Items;


    -- Return the result of the function
    RETURN @result;

END

--then alter table to add constraint --accept any price less or equal price on table

alter table Items add check(Price <= dbo.GetMaxPrice())

--after that, i try to insert items

insert into Items Values(3,4);
insert into Items Values(4,15); -- <-- here, I have problem. it inserted in Database..
--why inserted when max value is 5 ??

I use sql server 2012 Express, Win7

Upvotes: 3

Views: 2188

Answers (3)

StackUser
StackUser

Reputation: 5398

Try with this code. It is working fine for me.

CREATE TABLE item
  (
     ID    INT,
     Price MONEY
  );

--Insert some values
INSERT INTO item
VALUES      (1,
             4);

INSERT INTO item
VALUES      (2,
             5);

--create a function use above table
CREATE FUNCTION GetMax (@price MONEY)
RETURNS MONEY
AS
  BEGIN
      -- Declare the return variable here
      DECLARE @result MONEY = 5;

      SELECT @result = max(Price)
      FROM   item;

      IF @price < @result
        RETURN 1

      RETURN 0
  -- Return the result of the function
  END

--then alter table to add constraint --accept any price less or equal price on table
ALTER TABLE item
  WITH NOCHECK ADD CONSTRAINT ck1 CHECK(dbo.GetMax(Price)=(1))

--ALTER TABLE item
--  DROP CONSTRAINT ck1
--after that, i try to insert item
INSERT INTO item
VALUES      (3,
             4);

INSERT INTO item
VALUES      (4,
             15);

Upvotes: 2

Rhys Jones
Rhys Jones

Reputation: 5518

The problem you have is that the new value exists in the table (inside an implicit transaction) when the check constraint fires, so when you insert 15, the max(Price) is 15, so the constraint is satisfied, and the INSERT succeeds. I've had a thorough Google to try and find where this is documented but not found anything definitive.

An alternative approach to achieve the effect you are after would be to use an INSTEAD OF trigger, example below.

A word of advice though - this sort of validation strikes me as prone to going wrong somehow. I'd try and separate your limit values from the data - probably in another table.

Hope this helps,

Rhys

create table dbo.Items(
ID int,
Price money
);

insert into dbo.Items Values(1,4);
insert into dbo.Items Values(2,5);
go

create trigger trgItemsInsert on dbo.Items instead of insert as
begin
    -- Lookup current max price
    declare @MaxPrice money = (select max(Price) from dbo.Items)
    if exists (select 1 from inserted where Price > @MaxPrice)
        begin
            -- If there is a price greater than the current max then reject the insert
            declare @msg varchar(255) = 'Maximum allowed price is ' + cast(@MaxPrice as varchar(32)) + '.'
            rollback
            raiserror('%s', 16, 1, @msg)
        end
    else
        begin
            -- Otherwise perform the insert
            insert into dbo.Items 
                select ID,Price from inserted
        end
end
go

insert into dbo.Items Values(3,4);
insert into dbo.Items Values(4,15);
go
select * from dbo.Items
go

Upvotes: 4

Dave.Gugg
Dave.Gugg

Reputation: 6781

It appears the check constraint isn't executed until after the initial insert. So the database engine

  1. Inserts the data
  2. Checks the constraint
  3. If the insert violates the constraint, rollback the insert
  4. If the insert is ok, commit the data

You can see the order this occurs in the query execution plan: enter image description here

The description on the properties of the Assert highlighted in the picture says "Used to verify that a specified condition exists."

I am unable to find documentation on this from Microsoft, if anyone knows where to find it please let me know.

Upvotes: 2

Related Questions