1

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

  • 1
    Sure, as long as you have a row with the same value for the referenced column in the parent table. – Emyl Sep 25 '13 at 12:25
  • I wonder if you don't mean that if the column has value 0, it should refer to nothing? In that case you shouldn't have a default but rather leave it null. This is called an "optional foreign key". – Colin 't Hart Sep 25 '13 at 12:26
6

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

-3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.