CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Neo
Neo

Reputation: 16239

How to make foreign key as NULLABLE in SQL?

    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?

Upvotes: 2

Views: 6459

Answers (3)

NiralZaveri
NiralZaveri

Reputation: 21

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

Upvotes: 0

D Stanley
D Stanley

Reputation: 152596

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]) 
)

Upvotes: 0

sam yi
sam yi

Reputation: 4934

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]) 
)

Upvotes: 3

Related Questions