使用条件向SQL Server添加约束

gjmwrych 于 2023-02-11 发布在 SQL Server

关注(0)|答案(1)|浏览(154)

考虑下表

CREATE TABLE employee
(
    employee_number INT PRIMARY KEY, 
    employee_name NVARCHAR(100), 
    employee_year INT,
    manager_employee_number INT, 
    salary INT,

    FOREIGN KEY(manager_employee_number) 
         REFERENCES employee(employee_number)
);

我想添加一个约束条件，即每个员工的employee_year小于他的经理的employee_year，我们知道每个经理自己也是员工，所以我写了这样一个约束条件：

ALTER TABLE employee 
    ADD CONSTRAINT CK_EmployeeYearLessThanManager
        CHECK (employee_year <= (SELECT m.employee_year 
                                 FROM employee 
                                 WHERE m.employee_number = manager_employee_number ));

我得到这个错误：
此上下文中不允许使用子查询。只允许使用标量表达式
是否有可能编写这样的约束？

sql-server

来源：https://stackoverflow.com/questions/75364730/add-constraint-to-sql-server-with-condition

1条答案

按热度按时间

8fsztsew1#

从错误消息中可以看出，您不能将其作为子查询来实现，但作为一种解决办法，您可以创建一个标量值用户定义函数，并从check约束条件调用它。以下是一个示例：

CREATE TABLE employee
(
    employee_number INT PRIMARY KEY, 
    employee_name NVARCHAR(100), 
    employee_year INT,
    manager_employee_number INT, 
    salary INT,

    FOREIGN KEY(manager_employee_number) 
         REFERENCES employee(employee_number)
);
GO
CREATE FUNCTION dbo.get_manager_hire_year(@manager_employee_number INT)
RETURNS INT
AS
BEGIN
   DECLARE @e_year int;
   SELECT @e_year = employee_year
   FROM employee 
   WHERE employee_number = @manager_employee_number;

   RETURN @e_year
END
GO

ALTER TABLE employee 
    ADD CONSTRAINT CK_EmployeeYearLessThanManager
        CHECK (employee_year <= dbo.get_manager_hire_year(manager_employee_number))

赞(0）回复(0）举报 2023-02-11

我来回答

使用条件向SQL Server添加约束

1条答案

相关问题

热门标签

最新问答