Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

share|improve this question
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

1 Answer 1

up vote 1 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:

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 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:

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

share|improve this answer

Your Answer

 
discard

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.