Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question
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
up vote 2 down vote accepted

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 of zero as well. Example:

(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');

(SlaveID INT Primary Key,
 Description NVARCHAR(20),

 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 which allows you to create a Foreign Key without the required data existing in the master table. Likewise, Foreign Key constraints can be disable and enabled, but I would recommend against doing that for any normal circumstances. This is defined at:

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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