Can a foreign key have a default value equal to 0?

Question

I have a column whose default value is 0 .Can I make that column as foreign_key

Answer 1

Yes, you can define a column with a default value of 0 as a Foreign Key. However, for the constraint to work, you would need to have a row in the source table with a value of 0 as well. Example:

CREATE TABLE dbo.Master
(MasterID INT Primary Key,
 Name NVARCHAR(20));

INSERT into dbo.Master values (0,NULL);
INSERT into dbo.Master values (1,'Full');
INSERT into dbo.Master values (2,'Partial');

CREATE TABLE dbo.Slave
(SlaveID INT Primary Key,
 Description NVARCHAR(20),
 MasterID INT FOREIGN KEY REFERENCES dbo.Master(MasterID) DEFAULT (0));

 INSERT  INTO dbo.SLAVE (SlaveID, Description) VALUES(10,'Quickly');

This works fine. However, what if Master does not have a 0 MasterID. Then you would get:

Msg 547, Level 16, State 0, Line 15
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Slave__MasterID__3943762B". 
The conflict occurred in database "TEST", table "dbo.Master", column 'MasterID'.

There is a WITH NOCHECK option (in SQL Server) which allows you to create a Foreign Key without the required data existing in the master table. Likewise, Foreign Key constraints can be disabled and enabled, but I would recommend against doing that for any normal circumstances. This is defined at:

http://technet.microsoft.com/en-us/library/ms177463(v=sql.105).aspx

Answer 2

You can insert the 0 primary id record in the main table.