ALTER TABLE mit programmatisch festgelegter Konstante DEFAULT-Wert

Question

Ich versuche, eine Spalte (MSSQL 2005) zu einer Tabelle (Employee) mit einer Standardbeschränkung eines Primärschlüssels einer anderen Tabelle (Department) hinzuzufügen. Dann werde ich diese Spalte zu einem FK für diese Tabelle machen. Im Wesentlichen wird dies neue Mitarbeiter zu einer Basisabteilung auf der Grundlage des Abteilungsnamens zuweisen, wenn keine DepartmentID bereitgestellt wird.
Das funktioniert nicht:

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

Die Datenbanken "Produktion", "Test" und "Entwicklung" sind nicht mehr synchron, und die Abteilungs-ID für den Abteilungsnamen = "RocketScience" kann die gleiche sein oder auch nicht, so dass ich nicht einfach DEFAULT (irgendeine Zahl) sagen möchte. Ich erhalte immer wieder die Meldung "Variablen sind in der ALTER TABLE-Anweisung nicht zulässig", egal, wie ich das Problem angehe.
Wie geht man dabei richtig vor? Ich habe auch versucht, die select-Anweisung zu verschachteln, was dazu führt, dass ich "Subqueries are not allowed in this context. Nur skalare Ausdrücke sind erlaubt."

Außerdem könnte ich die Spaltenwerte in einer einzigen Anweisung auffüllen, anstatt die

{ALTER null}
{Werte aktualisieren}
{ALTER nicht null}

Schritte. Ich habe etwas über den Befehl WITH VALUES gelesen, konnte ihn aber nicht zum Laufen bringen. Danke!!!

Answer 1

Die akzeptierte Antwort hat gut funktioniert (Danke marc_s), aber nachdem ich eine Weile darüber nachgedacht hatte, beschloss ich, einen anderen Weg zu gehen.
Vor allem, weil eine Funktion auf dem Server verbleiben muss, die meines Erachtens jedes Mal aufgerufen wird, wenn ein Mitarbeiter hinzugefügt wird.
Wenn später jemand an der Funktion herumpfuscht, kann niemand mehr einen Mitarbeiter eintragen, und der Grund dafür wäre nicht ersichtlich. (Selbst wenn dies nicht der Fall ist, gibt es immer noch zusätzliche Funktionen auf dem Server, die nicht vorhanden sein müssen).

Ich habe den Befehl dynamisch in einer Variablen zusammengestellt und diese dann mit dem Befehl EXECUTE aufgerufen.

Nicht nur das, sondern da ich das Schlüsselwort DEFAULT mit NOT NULL verwendet habe, wurde die Tabelle wieder aufgefüllt und ich musste nicht mehrere Befehle ausführen, um es zu erledigen. Das habe ich durch Glück herausgefunden...

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 2

Sie könnten den Code zum Auffinden Ihrer Abteilungs-ID in eine gespeicherte Funktion einschließen und diese in Ihrer DEFAULT-Beschränkungsanweisung verwenden:

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

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

  RETURN @DepartmentID
END

Und dann:

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

Ist das hilfreich?

Marc

Answer 3

Wenn Sie Ihre Fremdschlüssel-Beschränkung anwenden, nachdem Sie die Spalte hinzugefügt haben, können Sie beim Ändern der Tabelle einen beliebigen Wert für den Standardwert verwenden. Dann können Sie eine Aktualisierungsanweisung mit Ihrem Variablenwert ausführen.

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;