locked
Parent-child relationship allows NULL in child RRS feed

  • Question

  • Created two tables with a Parent and Child relationship. Parent "Id" is used as child "FK". Parent does not all NULL values in Id but can INSERT NULL  value into Foreign Key of child. Why is this not prevented by the constraint?

    TIA,

    edm2

    CREATE TABLE [dbo].[Parent](
    	[PId] [int] IDENTITY(1,1) NOT NULL,
    	[Name] [varchar](50) NULL,
     CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED 
    (
    	[PId] ASC
    )
    )
    
    insert into Parent values('a')
    insert into Parent values ('b')
    
    
    
    CREATE TABLE [dbo].[Child](
    	[CId] [int] IDENTITY(1,1) NOT NULL,
    	[PId] [int] NULL,
    	[Name] [varchar](50) NULL,
     CONSTRAINT [PK_Child] PRIMARY KEY CLUSTERED 
    (
    	[CId] ASC
    )
    )
    
    
    ALTER TABLE [dbo].[Child]  WITH CHECK ADD  CONSTRAINT [FK_Child_Parent] FOREIGN KEY([PId])
    REFERENCES [dbo].[Parent] ([PId])
    
    ALTER TABLE [dbo].[Child] CHECK CONSTRAINT [FK_Child_Parent]
    GO
    
    insert into child values(1,'q')
    insert into child values(NULL,'m')
    
    select * from child


    • Edited by edm2 Monday, June 24, 2013 11:03 PM
    Monday, June 24, 2013 11:03 PM

Answers