I'm trying to add a non-nullable
column to an already existing table, and I read somewhere that I need a DEFAULT
value in order to do that.
The value for the DEFAULT
needs to be equal to the TOP 1 user_id
result of the SELECT
query shown below. I tried using a variable, but this didn't work.
This is what I tried:
DECLARE @latestuserid AS int
SELECT TOP 1 @latestuserid = user_id from Employees ORDER BY user_id DESC
ALTER TABLE Control ADD nextuserid INT NOT NULL DEFAULT (@latestuserid)
However I get the error:
Variables are not allowed in the
ALTER TABLE
statement.
If I can't use variables, how do I do this if I don't always know the exact value of that SELECT
statement?
You can first define the desired column as nullable and then execute an update command for the new column. After that, you can make it not nullable.