ALTER TABLE，以编程方式确定的恒定默认值 [英] ALTER TABLE with programmatically determined constant DEFAULT value

问题描述

我正在尝试将表（Employee）的列（MSSQL 2005）添加到另一个表（部门）的主键的默认约束下。然后，我将使该列成为该表的FK。本质上，如果未提供DepartmentID，这将根据部门名称将新员工分配到基础部门。

这不起作用：

I am trying to add a column (MSSQL 2005) to a table (Employee) with a default constraint of a primary key of another table (Department). Then I am going to make this column a FK to that table. Essentially this will assign new employees to a base department based off the department name if no DepartmentID is provided.
This does not work:

DECLARE     @ErrorVar       INT
DECLARE     @DepartmentID       INT

SELECT      @DepartmentID = DepartmentID
FROM        Department
WHERE       RealName = 'RocketScience'

ALTER TABLE     [Employee]
ADD             [DepartmentID] INT NULL
CONSTRAINT      [DepartmentIDOfAssociate] DEFAULT (@DepartmentIDAssociate)
SELECT @ErrorVar = @@Error
IF (@ErrorVar <> 0)
BEGIN
    GOTO FATAL_EXIT
END

生产，测试和开发数据库已不同步，DepartmentName ='RocketScience'的DepartmentID可能相同也可能不同，所以我不想只说DEFAULT（某个数字）。无论采用哪种方式解决问题，我都不断收到 ALTER TABLE语句中不允许使用变量的信息。
正确的方法是什么？我也尝试嵌套选择语句，该语句得到在这种情况下不允许子查询。仅允许标量表达式。


此外，真的很棒，我可以在一个语句中填充列值，而不是执行

{ALTER null}
{更新值}
{ALTER not null}

步骤。我阅读了一些有关WITH VALUES命令的信息，但无法使其正常工作。
谢谢!!!

The Production, Test, and Development databases have grown out of synch and the DepartmentID for the DepartmentName = ‘RocketScience’ may or may not be the same so I don’t want to just say DEFAULT (somenumber). I keep getting "Variables are not allowed in the ALTER TABLE statement" no matter which way I attack the problem.
What is the correct way to do this? I have tried nesting the select statement as well which gets "Subqueries are not allowed in this context. Only scalar expressions are allowed."

In Addition, what would be really great I could populate the column values in one statement instead of doing the

{ALTER null}
{Update values}
{ALTER not null}

steps. I read something about the WITH VALUES command but could not get it to work. Thanks!!!

推荐答案

您可以将代码包装起来，以将部门ID查找到存储的函数中并使用它在您的默认约束语句中：

You could wrap the code to find your department ID into a stored function and use that in your DEFAULT constraint statement:

CREATE FUNCTION dbo.GetDepartment()
RETURNS INT
AS
BEGIN
  DECLARE         @DepartmentID           INT

  SELECT          @DepartmentID = DepartmentID
  FROM            Department
  WHERE           RealName = 'RocketScience'

  RETURN @DepartmentID
END

然后：

ALTER TABLE     [Employee]
ADD                     [DepartmentID] INT NULL
CONSTRAINT      [DepartmentIDOfAssociate] DEFAULT (dbo.GetDepartment())

有帮助吗？

Marc

这篇关于ALTER TABLE，以编程方式确定的恒定默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！