英文:
Add constraint to SQL Server with condition
问题
我想添加一个约束，要求每个员工的employee_year都小于其经理的employee_year，并且我们知道每个经理本身也是一名员工。因此，我编写了以下代码：
ALTER TABLE employee
ADD CONSTRAINT CK_EmployeeYearLessThanManager
CHECK (employee_year <= (SELECT m.employee_year
FROM employee m
WHERE m.employee_number = manager_employee_number ));
我遇到了以下错误：
不允许在此上下文中使用子查询。只允许使用标量表达式。
是否有可能以某种方式编写这样的约束？
英文:
Consider this table
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)
);
I want to add a constraint which every employee's employee_year is less than his manager's and we know every manager is an employee himself. So I wrote this:
ALTER TABLE employee
ADD CONSTRAINT CK_EmployeeYearLessThanManager
CHECK (employee_year <= (SELECT m.employee_year
FROM employee
WHERE m.employee_number = manager_employee_number ));
I get this error:
> Subqueries are not allowed in this context. Only scalar expressions are allowed
Is there any possible way to write such a constraint?
答案1
得分: 1
根据错误信息可以看出，你不能将它实现为子查询，但作为一种解决方法，你可以创建一个标量值的用户定义函数，并从检查约束中调用它。这里有一个示例：
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))
英文:
As it can be seen from the error message, you cannot implement it as a subquery, but as a work around you can create a scalar-valued user defined function and call it from the check constraint. Here is an example:
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))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论