主页 / dba / 问题 / 324297
Accepted
RobMartin
Asked: 2023-03-03 00:27:26 +0800 CST

更改在 SQL Server 2012 中的检查约束中使用的标量 UDF

  • 772

我对使用标量 UDF 的列有一个检查约束。我有一个需要更改 UDF 的要求。据我所知，我需要删除约束，对 UDF 进行更改，然后重新添加约束。在取消约束期间，我担心表中可能会插入坏数据。有没有办法在不删除约束的情况下更改 UDF？如果不是，有没有办法防止坏数据进入表？

sql-server-2012

3 个回答

  1. Best Answer

    你不应该

    当您将标量 UDF 放入计算列或检查约束时，您会遇到两个问题：

    • 接触表的查询将无法生成并行查询计划（此处有更多详细信息）
    • 如果它们没有持久化或没有索引，它们将在处理查询所需的每行中执行一次

    在可能的情况：

    • 在函数外部使用纯 T-SQL 进行计算（如果所有列都在一个表中则可能）
    • 编写一个触发器来捕获不应该放入表中的行
    • 使用不同类型的约束（外键等）来捕获表中不应存在的行

    照本宣科

    /*A table*/
CREATE TABLE
    dbo.bad_idea
(
    id int PRIMARY KEY IDENTITY,
    some_date date
);
GO 

/*A silly function*/
CREATE FUNCTION
    dbo.please_do_not
(
    @some_date date
)
RETURNS bit
AS
BEGIN
      RETURN 
      (
          CASE 
              WHEN @some_date < '19000101' 
              THEN 0 
              ELSE 1 
          END
      );
END;
GO 

/*A bad idea for a check constraint*/
ALTER TABLE
    dbo.bad_idea
WITH CHECK
ADD CONSTRAINT  
    god_no 
CHECK 
(
    dbo.please_do_not(some_date) = 1
);
GO 

/*An updated silly function*/
CREATE FUNCTION
    dbo.please_i_beg_you_do_not
(
    @some_date date
)
RETURNS bit
AS
BEGIN
      RETURN 
      (
          CASE 
              WHEN @some_date < '19800101' 
              THEN 0 
              ELSE 1 
          END
      );
END;
GO 

/*Another bad idea*/
ALTER TABLE
    dbo.bad_idea
WITH CHECK
ADD CONSTRAINT  
    god_no 
CHECK 
(
    dbo.please_do_not(some_date) = 1
);
GO 

/*An equally bad idea*/
ALTER TABLE
    dbo.bad_idea
WITH CHECK
ADD CONSTRAINT  
    god_no_please
CHECK 
(
    dbo.please_i_beg_you_do_not(some_date) = 1
);
GO 

/*Drop the old one off*/
ALTER TABLE
    dbo.bad_idea
DROP CONSTRAINT
    god_no;
    • 5

  2. 不，不幸的是，您不能更改检查约束使用的函数，即使在使用 NOCHECK 禁用时也是如此。

    您可以通过以下方式解决此问题（没有不良数据的风险）：-

    1. 使用新定义创建第二个函数
    2. 使用新函数创建第二个检查约束
    3. 删除原始检查约束
    4. 放弃第一个功能

    或者，如果您需要保留检查约束和函数的名称，您可以通过以下方式实现：-

    1. 使用新定义创建第二个函数
    2. 使用新函数创建第二个检查约束
    3. 删除原始检查约束
    4. 用新定义改变第一个函数
    5. 重新创建原始检查约束
    6. 删除第二个检查约束
    7. 删除第二个功能
    • 3

  3. 您可以在一个事务中完成所有操作，这将Sch-M在表上强制执行模式修改锁，以防止插入任何数据。

    例如：

    BEGIN TRAN;

ALTER TABLE YourTable
DROP CONSTRAINT YourConstraint;

GO

CREATE OR ALTER FUNCTION dbo.YourFunction.....

/*
etc
*/

GO

ALTER TABLE YourTable
ADD CONSTRAINT YourConstraint CHECK (dbo.YourFunction(YourColumn) = 1);

COMMIT;

    我同意检查约束中的函数是个坏主意，应该尽可能避免。

    需要这样一个函数的一个常见原因是创建一个多表约束。然而，这可以使用一个鲜为人知的技巧使用索引视图来更好地完成。一种效率稍低的方法是触发器。

    • 2

相关问题