ALTER TABLE with software defined constant value DEFAULT

Question

I am trying to add a column (MSSQL 2005) to a table (Employee) with a default restriction on the primary key of another table (Department). Then I am going to make this FK column for this table. In essence, this will appoint new employees to the base department, based on the department’s name, if the department is not 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

Production, Test Development - , DepartmentID DepartmentName = 'RocketScience , DEFAULT (somenumber). " ALTER TABLE" , ​​.
? select, " . ".

, , ,

{ALTER null}
{ }
{ALTER not null}

. - WITH VALUES, . !!!

Answer 1

, DEFAULT:

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())

?

Answer 2

( marc_s), , , .
, ​​, , , , .
- , , . ( , , )

, , , EXECUTE.

, DEFAULT NOT NULL, , , . , ...

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

Answer 3

If you apply a foreign key constraint after adding a column, you can use any value for the default value when changing the table. Then you can run the 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;