Is it required to make foreign key column in a table as NOT NULL, If we not written explicitly foreign key column as not null what it will be? Can it contain null values?
And what is the Difference between following two statements:
[PhoneId] [int] NOT NULL FOREIGN KEY REFERENCES [dbo].[tbl_PhoneNumber](PhoneNumberId) [PhoneId] [int] FOREIGN KEY REFERENCES [dbo].[tbl_PhoneNumber](PhoneNumberId) Is it required to make foreign key column in a table as NOT NULL,
No it is not required. MSDN says that:-
When a value other than NULL is entered into the column of a FOREIGN KEY constraint, the value must exist in the referenced column; otherwise, a foreign key violation error message is returned. To make sure that all values of a composite foreign key constraint are verified, specify NOT NULL on all the participating columns.
So the simple answer to your question is NO IT IS NOT REQUIRED.
A foreign key attribute can contain NULL values as well.
Your second definition will allow Nulls in the column.
From here:-
When a FOREIGN KEY constraint is added to an existing column or columns in the table SQL Server, by default checks the existing data in the columns to ensure that all values, except NULL, exist in the column(s) of the referenced PRIMARY KEY or UNIQUE constraint.
Also check Foreign Key Constraints
According to sql normalization rule foreign key value must be equal to primary key value or NULL, so it will contain either NULL value of one value from primary key table row.
It is not required. A foreign key attribute without NOT NULL can contain NULL values, and this can be used to indicate that no such tuple in the referenced relation is applicable.
1 :: 0..nin the first and0..1 :: 0..nin the second (if you are familiar with this notation).SET ANSI_NULL_DFLT_ON