I have a column whose default value is 0 .Can I make that column as foreign_key
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: