Alter Table Add a column with a default value and FK, this value does not exist in the FK reference data

Question

These are my tables:

Member : Identifier, ....

Product : identifier, ...

My Member Table has some none values ​​if they have Id = 0, and I don't want to add any element with Id = 0, so I'm trying 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:

 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 a new error:

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

If I try to create all the tables again, everything will be fine if I lose my data, so you will need to back up, create the tables and restore the data. So is there a way to change the table with this situation? What is your suggestion?

Answer 1

The only "value" you can have in the link table, such that the foreign key constraint is not applied, is NULL . Not 0 , or any other magical meaning.

So the obvious solution is to resolve NULL s:

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

Answer 2

your "alter table" is the best way to do this. But first you add a table with a value of "0", and this is a "FOREIGN KEY", but you do not have a member with a value of "0", so you get an error.

the best way is to know the .alter table and then make a true value for the new column and then change the column and set it to "FOREIGN KEY"