Alter Table Add Column with Default value And FK, that Value no existing in FK Reference Data

Question

This is My Tables :

Member : Id, ....

Product: Id, ....

My Member Table have some values none if them with Id = 0 and I don't want to add any member with Id = 0, So I try to run this Script:

ALTER TABLE [Product]
ADD [Member_Id] BIGINT  NOT NULL  DEFAULT(0),
CONSTRAINT [FK_Product_Member] FOREIGN KEY ([Member_Id]) REFERENCES [Member];

So There is an Error :

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Product_Member".

So I try this one:

SET IDENTITY_INSERT [Member] ON
INSERT INTO [Member] ([Id]) VALUES  (0);
SET IDENTITY_INSERT [Member] OFF

ALTER TABLE [Product]
ADD [Member_Id] BIGINT  NOT NULL  DEFAULT(0),
CONSTRAINT [FK_Product_Member] FOREIGN KEY ([Member_Id]) REFERENCES [Member];

DELETE FROM [Member] WHERE [Member].[Id] = 0;

Then The new Error is:

The DELETE statement conflicted with the REFERENCE constraint "FK_Product_Member".

If I try to create all Tables again, every thing will be OK of course with lost my Data so need to get backup, create tables and restore data. So Is there any way to alter Table with this situation? what is your suggestion?

Answer 1

The only "value" that you can have in a referencing table, such that the foreign key constraint is not enforced, is NULL. Not 0, or any other magic value.

So the obvious solution is to allow NULLs:

ALTER TABLE [Product]
ADD [Member_Id] BIGINT  NULL,
CONSTRAINT [FK_Product_Member] FOREIGN KEY ([Member_Id]) REFERENCES [Member];