CODEWITHSUNDEEP
sqlsql-server
J. Michiels
J. Michiels

Reputation: 335

SQL Server: User Defined Function In Check Constraint

I have a table that tracks whether data has been transferred for a certain period. Here's a short example (left out a lot of columns, but this should suffice):

+----+--------+------------+------+
| Id | DealId |    Date    | Sent |
+----+--------+------------+------+
|  1 |      1 | 2018-01-01 |    1 |
|  2 |      1 | 2018-02-01 |    1 |
|  3 |      1 | 2018-03-01 |    0 |
|  4 |      2 | 2018-01-01 |    1 |
|  5 |      2 | 2018-02-01 |    1 |
|  6 |      2 | 2018-03-01 |    0 |
|  7 |      1 | 2018-04-01 |    0 |
+----+--------+------------+------+

I want to create a check constraint that prevents the insertion of the last record. It should not be allowed to have more than one Sent = 0 line per DealId. In other words: it should not even be possible to insert a line for a DealId where Sent = 0 if there already is a Sent = 0 line for that deal.

Here's the table script with the constraint:

    CREATE TABLE [Mrd].[Snapshot]
    (
        [Id] INT IDENTITY(1,1) CONSTRAINT [PK_Mrd_Snapshot_Id] PRIMARY KEY,
        [DealId] INT NOT NULL,
        [Date] DATETIME NOT NULL,
        [Sent] BIT NOT NULL CONSTRAINT [DF_Mrd_Snapshot_Sent] DEFAULT 0,

        CONSTRAINT [CK_Mrd_Snapshot_Sent]
        CHECK ([Function].[ValidateSent]([DealId]) = 1)
)

Here's the function script:

CREATE FUNCTION [Function].[ValidateSent]
(
    @DealId INT
)
RETURNS BIT
AS
BEGIN
    IF ((SELECT COUNT(*) FROM [Mrd].[Snapshot] WHERE [DealId] = @DealId AND [Sent] = 0) = 0)
    BEGIN
        RETURN 1;
    END;    

    RETURN 0;
END

And the schema creation for quick copy paste:

CREATE SCHEMA [Function]

With this implementation in place, I can't insert any line with Sent = 0. It throws a conflict error with the check constraint.

Any suggestions about what I'm doing wrong in the constraint or function? Or perhaps there is a better way to do this check?

Thanks!

Upvotes: 4

Views: 1285

Answers (2)

Tab Alleman
Tab Alleman

Reputation: 31785

For what it's worth, the reason you were getting the error with your code is because when a CHECK CONSTRAINT calls a function, the function code assumes that the row that is triggering the call has already been created/inserted/updated.

So in your function you are testing whether there are zero rows that have Sent=0. The row being inserted COUNTS as a row in the table already, so if you only allow zero rows to have Sent=0 then you will never be able to insert even a single row with Sent=0.

To do what you want, you should have only allowed one row per id to have Sent=0.

Upvotes: 2

sniperd
sniperd

Reputation: 5274

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.

Upvotes: 5

Related Questions