代码之家 › 专栏 › 技术社区 › J. Michiels

SQL Server:检查约束中的用户定义函数

sql-server sql

J. Michiels · 技术社区 · 7 年前

+----+--------+------------+------+
| 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 |
+----+--------+------------+------+

我想创建一个检查约束,以防止插入最后一条记录。不允许每个dealid有多个sent=0行。

    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)
)

以下是函数脚本:

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

CREATE SCHEMA [Function]

或者也许有更好的方法来做这个检查?

2 回复 | 直到 7 年前

sniperd Ali Ahmed 7 年前

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

dealid sent WHERE ([sent]=0) ,这应该满足您只希望在以下情况下应用规则的要求: sent = 0