Custom Function with Check Constraint SQL Server 2008

Custom function with check constraint SQL Server 2008

As stated by Martin Smith using a check constraint with a UDF has some problems and might have a negative impact on performance, but if you want to try it anyway this code should work:

CREATE FUNCTION dbo.CheckVenueCapacity (@venue_id int, @capacity int)
RETURNS int
AS 
BEGIN
  DECLARE @retval int
    SELECT @retval = CASE WHEN venue_max_capacity >= @capacity THEN 0 ELSE 1 END
    FROM venues
    WHERE venue_id = @venue_id 
  RETURN @retval
END;
GO

ALTER TABLE events 
  ADD CONSTRAINT chkVenueCapacity 
  CHECK (dbo.CheckVenueCapacity(event_venue_id, event_expected_attendance) = 0); 

SQL Server: User Defined Function In Check Constraint

I am personally in favor of using unique indexes instead of a constraint for cases like this. I have found that columns I want to be unique I probably want an index anyways, so 2 birds with 1 stone. So I would do this:

CREATE UNIQUE NONCLUSTERED INDEX [uidx_dealid_sent] ON [dbo].[Snapshot]
(
    [dealid] ASC,
    [sent] ASC
)
WHERE ([sent]=0)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

The compound unique index does what you are looking to do: You won't be able to insert a row where a specific dealid and specific sent already exist together. As Larnu pointed out, this should be a filtered index (for MSSQL 2008 and later). Notice the WHERE ([sent]=0), this should satisfy your requirement for only wanting to apply the rule when sent = 0.

Check Constraint Which Uses A Custom Function

For 1)

Have you tried:

ALTER TABLE dbo.LiaQueue 
ADD CONSTRAINT CK_LiaQueue_ReqNo_Unique 
CHECK (dbo.[ReqNoIsUniquePerReserve](ReqNo,[Type]) = 1)

For 2), I'm not so sure what you mean.

Create custom error message in check constraints in SQL SERVER 2008

You could name your constraint with a user message.

For Example:

ADD CONSTRAINT 
[Foo cannot be greater than Bar. Please be sure to check your foos and bars next time.] 
CHECK (foo <= Bar)

Check constraint using function

The problem is that the INSERT happens BEFORE your CHECK constraint runs, so there is always an user in the table before the function runs. Change your function to this:

ALTER FUNCTION [dbo].[isActiveUsername]
    (@username nvarchar(15))
RETURNS bit
AS
BEGIN
    IF  (SELECT COUNT(*) FROM Users WHERE isActive = 1 AND UserName = @username) > 1
    BEGIN
        RETURN 1
    END

    RETURN 0

END

Is it possible to add a check constraint that calls a user defined function in a different database?

You shouldn't need to do this in a CHECK CONSTRAINT. An AFTER INSERT, UPDATE Trigger should be able to provide the same functionality as the CHECK CONSTRAINT. You just need to cancel the INSERT or UPDATE operation if the desired condition is (or is not) met. And this is easily done simply by issuing a ROLLBACK, which works due to Triggers existing within the transaction that is the DML statement itself. Hence, just do something along the lines of:

CREATE TRIGGER dbo.trCheckSomeField
ON dbo.SomeTable
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON;

IF (EXISTS(
        SELECT *
        FROM   Inserted ins
        WHERE  Utility.SQL#.RegEx_IsMatch(ins.SomeField, ...) = 0
          )
    )
BEGIN;
  ROLLBACK TRAN;
  RAISERROR('Your data suck!', 16, 1);
  RETURN;
END;

function do not work in CHECK Constraint on sql server

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

---

Related Topics