How to make foreign key as NULLABLE in SQL?

Question

    CREATE TABLE [M].[SocialInfo]
    (
        [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT newid(), 
[MemberId] UNIQUEIDENTIFIER  DEFAULT newid(),
    [GroupId] UNIQUEIDENTIFIER  DEFAULT newid(),
        [NewsURL] VARCHAR(200) NULL,     
        CONSTRAINT [FK_SocialInfo_Member] FOREIGN KEY ([MemberId]) REFERENCES [M].[Member]([Id]), 
        CONSTRAINT [FK_SocialInfo_Group] FOREIGN KEY ([GroupId]) REFERENCES [M].[Group]([Id]) 
    )

How can I make both FK nullable?

I'm unable to get the syntax?

http://stackoverflow.com/questions/2366854/can-table-columns-with-a-foreign-key-be-null — sarwar026, Dec 26 '13 at 15:23
Different question now altogether... you can't do default newid() on a fk column. The newid that has not been created yet will need to exist in the Member/Group table before you're allowed to insert it into SocialInfo table. Look into using triggers for what you're trying to do. — sam yi, Dec 26 '13 at 15:29
Do not use `NEWID()` as it will fragment your indexes by inserting seemingly random GUID strings. You are much better off using `NEWSEQUENTIALID` or even better by using an INT or BIG into as your ID values. — Zane, Dec 26 '13 at 15:34
It's probably better to remove default newid() and just wrap all this in a store procedure so that it insert with default newid() in Member/Group table then use that to insert into SocialInfo — sam yi, Dec 26 '13 at 15:49

score 2 · Accepted Answer · answered Dec 26 '13 at 15:23

I think you just left out the column creation piece.

CREATE TABLE [M].[SocialInfo]
(
    [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT newid(), 
    [NewsURL] VARCHAR(200) NULL,     
    MemberId INT NULL,
    GroupId INT NULL,
    CONSTRAINT [FK_SocialInfo_Member] FOREIGN KEY ([MemberId]) REFERENCES [M].[Member]([Id]), 
    CONSTRAINT [FK_SocialInfo_Group] FOREIGN KEY ([GroupId]) REFERENCES [M].[Group]([Id]) 
)

score 0 · Answer 2 · answered Dec 26 '13 at 15:30

0

A default value for a foreign key makes no sense - use NULL instead

CREATE TABLE [M].[SocialInfo]
(
    [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT newid(), 
    [MemberId] UNIQUEIDENTIFIER  NULL,
    [GroupId] UNIQUEIDENTIFIER  NULL,
    [NewsURL] VARCHAR(200) NULL,     
    CONSTRAINT [FK_SocialInfo_Member] FOREIGN KEY ([MemberId]) REFERENCES [M].[Member]([Id]), 
    CONSTRAINT [FK_SocialInfo_Group] FOREIGN KEY ([GroupId]) REFERENCES [M].[Group]([Id]) 
)

answered Dec 26 '13 at 15:30

D Stanley

149,601
11
178
240

2

I disagree. I prefer the approach of a default value pointing to a record in the parent table that means something like Not Applicable. – Dan Bracuk Dec 26 '13 at 16:10

score 0 · Answer 3 · answered Dec 26 '13 at 15:37

I think you need to put NULL keyword as it is shown in below link.

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/325c11b9-e5c4-4db7-94e6-f82b44412b9b/is-it-possible-to-allow-null-values-in-foreign-key?forum=transactsql

Also this sqlfiddle may be helpful

http://sqlfiddle.com/#!3/39a0b/10

How to make foreign key as NULLABLE in SQL?

3 Answers3