ALTER TABLE with programmatically determined const

问题:

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

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

回答1:

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

And then:

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

Does that help?

Marc

回答2:

The accepted answer worked great (Thanks marc_s) but after I thought about it for a while I decided to go another route.
Mainly because there has to be a function left on the server which I think ends up being called every time an employee is added.
If someone messed with the function later then no one could enter an employee and the reason would not be obvious. (Even if that is not true then there are still extra functions on the server that do not need to be there)

What I did was assemble the command dynamically in a variable and then call that using the EXECUTE command.

Not only that but since I used the DEFAULT keyword with NOT NULL the table was back populated and I didn't have to run multiple commands to get it done. I found that one out by luck...

DECLARE     @ErrorVar                   INT
DECLARE     @DepartmentIDRocketScience          INT
DECLARE     @ExecuteString                  NVARCHAR(MAX)

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

SET @ExecuteString = ''
SET @ExecuteString = @ExecuteString + 'ALTER TABLE      [Employee] '
SET @ExecuteString = @ExecuteString + 'ADD              [DepartmentID] INT NOT NULL '
SET @ExecuteString = @ExecuteString + 'CONSTRAINT       [DF_DepartmentID_RocketScienceDepartmentID] DEFAULT ' +CAST(@DepartmentIDAssociate AS NVARCHAR(MAX))
EXECUTE (@ExecuteString)
SELECT @ErrorVar = @@Error
IF (@ErrorVar <> 0)
BEGIN
    GOTO FATAL_EXIT
END

回答3:

If you apply your foreign key constraint after adding the column, you can use any value for the default value when altering the table. Then you can run an update statement with your variable value.

ALTER TABLE Employee 
ADD DepartmentID INT NOT NULL
Default -1;

UPDATE Employee
SET Employee.DepartmentID = @DepartmentID
WHERE DepartmentID = -1;

ALTER TABLE Employee 
ADD CONSTRAINT fk_employee_to_department FOREIGN KEY (DepartmentID) 
REFERENCES Department;