How do you add a NOT NULL FOREIGN KEY column to an existing (populated) table in MS SQL?

Question

I need to add a NOT NULL column to an existing (populated) table that will be a foreign key to another table. This brings about two problems:

1. When you add the column, its value cannot be null - using a default is not an option (unless it is removed later on) because the database logic is used in the server side validation when a user enters a new record i.e. when the application tries to add a record with this field having a null value, the database throws an error that the application catches and returns to the user, prompting them to correct it.

2. The column has a foreign key constraint, meaning its value MUST exist in the foreign table as well.

What is the best way to go about this?

Answer 1

Create the column, but allow NULL. Populate the column with the correct data from the foreign key table. Alter the column add not null. Add the foreign key constraint.

Answer 2

About parts of your questions (ofc after years):

1.If you mean that the default value would be something so smart, and you wouldn`t need to change it in future, then your wish is wrong. why?

for two reasons:

• a) In the concept of Default value (in every where programming
  languages, DBs and so on...) the default value is not something which replace the value dynamically with something you want. For example Func Sum(a,b,c=10), in this situation if you don't enter parameter c, it would take it as 10, otherwise you should enter
  something instead. so default values are predictable and calculable
  values, NOT smart values.
• b) Foreign keys are something even more sensitive than a optional
  parameter in a method, coz of the relational meanings in RDBMSs so u surely should edit that in future, even maybe sometimes it change
  over and over base on usages of DB.

2.It can be handle with the code i`ll show you.

Therefore base on this explanations you could have a column with default value which exist in Fkeys, but it won`t be something you need, and you should update it in future base on your usage. and for this you need:

FIRST:

• Create a function that return a valid and exist foreign key of specific table like this:

CREATE FUNCTION SelectMinForeignKey()
RETURNS INT
AS
BEGIN
  DECLARE @FirstID INT
  SELECT @FirstID = MIN(ID) from DetailTableExample01
  RETURN @FirstID
END

SECOND:

• Then you should alter your table to add column like this:

ALTER TABLE example1 ADD NoNullableCol INT NOT NULL DEFAULT [dbo].SelectMinForeignKey()

• or with adding Relation instantly:

  ALTER TABLE example1 
    ADD NoNullableCol2 INT NOT NULL  DEFAULT [dbo].SelectMinForeignKey() ,
    FOREIGN KEY(NoNullableCol2) REFERENCES DetailTableExample01(id);

• or more complete with adding constraint instantly and assignable FK name:

ALTER TABLE dbo.example1 ADD
    NoNullableCol INT NOT NULL  DEFAULT [dbo].SelectMinForeignKey(),
    CONSTRAINT FK_example1_DetailTableExample01
    FOREIGN KEY (NoNullableCol) 
    REFERENCES dbo.DetailTableExample01 (ID) 
     ON UPDATE  CASCADE 
     ON DELETE  CASCADE;

• or:

ALTER TABLE dbo.example1 ADD
    NoNullableCol INT NOT NULL  DEFAULT [dbo].SelectMinForeignKey()
GO
ALTER TABLE dbo.example1 ADD 
    CONSTRAINT FK_example1_DetailTableExample01
    FOREIGN KEY (NoNullableCol) 
    REFERENCES dbo.DetailTableExample01 (ID) 
     ON UPDATE  CASCADE 
     ON DELETE  CASCADE

NOTE: As you know table and column names are sample.

THIRD:

• Now you should change values in NoNullableCol as you want

All-in-One:

• The entire query would be something like this

CREATE FUNCTION SelectMinForeignKey()
RETURNS INT
AS
BEGIN
  DECLARE @FirstID INT
  SELECT @FirstID = MIN(ID) from DetailTableExample01
  RETURN @FirstID
END

GO

ALTER TABLE dbo.example1 ADD
    NoNullableCol INT NOT NULL  DEFAULT [dbo].SelectMinForeignKey(),
    CONSTRAINT FK_example1_DetailTableExample01
    FOREIGN KEY (NoNullableCol) 
    REFERENCES dbo.DetailTableExample01 (ID) 
     ON UPDATE  CASCADE 
     ON DELETE  CASCADE;

AND It`s Done!

Hope it solve your problem