none
foreign key and null values

    Question

  • Hi, I have a problem regarding null values in foreign keys. I have tried to insert a rows that has some null values in fields that are under foreign key bond but I receive an error: "impossible to insert null value" . One of you knows if is possible to insert null values in fields that are under foreign key bond?
    Thanks
    Francesco
    Wednesday, November 04, 2009 5:01 PM

All replies

  • You can insert NULL only if referenced table has NULL value for that column.

    If you have defined primary key on source table then you can certainly not insert NULL but if source table has unique key defined on referenced column then you can insert NULL in your foreign key table.
    Wednesday, November 04, 2009 5:16 PM
  • It sounds like the fields are set to NOT NULL.  Can you look at the table in SSMS and see if they are defined as NOT NULL?


    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer
    Wednesday, November 04, 2009 5:16 PM
  • Hi, I have checked my table and fields are not set to not null.
    Mohan you say that if the surce table has a unique key defined on reference column I can't insert null in my foreign key table, but i have other database with tables where the case happens and I can insert null values! I have found also this that say that insert null values in foreign key is possible http://msdn.microsoft.com/en-us/library/aa933117(SQL.80).aspx.
    I have checked my database with others and I have found a difference: triggers. Maybe I have to modify or delete them??
    One ideas?
    Thanks, Francesco
    Wednesday, November 04, 2009 10:22 PM
  • Francesco,

    Check if there is a trigger in that table, trying to insert somewhere else.


    AMB
    Thursday, November 05, 2009 12:20 AM
  • Hi Francesco

    I said:
    You can insert NULL only if referenced table has NULL value for that column.

    If you have defined primary key on source table then you can certainly not insert NULL but if source table has unique key defined on referenced column then you can insert NULL in your foreign key table.

    In other words, If you can insert NULL into source table for column that has foreign key reference set then you can insert NULL in your foreign key table.
    Thursday, November 05, 2009 3:40 AM
  • Hi Mohan,
    my souce column for the foreign key is the primary key of the table (and of course I can't insert null values). But I have other db in sql server 2k5 that have table with foreign key (that relates to a primary key column of the source table) where I can insert null values and only values that are present in my source table. You say that this is impossible but it's so...I have checked my triggers in my old sql 2k5 there's no trigger but the table works perfectly I can insert null values or values that are present in my referenced table.
    In my new table of sql server 2k8 there are 3 trigger (insert, update, delete) but if I delete them the foreign key doesn't work no more.
    Have you any solution?
    Thanks.
    Francesco
    Friday, November 06, 2009 10:31 AM
  • Francesco,

    Are you enforcing a reference constraint using triggers in the same database?

    If so, think seriously about implementing this using a foreign key constraint.


    AMB
    Friday, November 06, 2009 8:25 PM
  • Hi, I have do nothing! I don't have created triggers, what I have done is only create a foreign key constraint in the database diagrams (in a user friendly way, I'm not able to create triggers by myself). Maybe there's some settings in sql server that command how to create foreign constraints?
    I have try to eliminate the treiggers and the foreign key still reamains, but it doesn't work no more: I can enter every value I want.
    Thanks, Francesco
    Monday, November 09, 2009 1:55 PM
  • I do not think we could help much if we are not able to reproduce the problem.


    use tempdb;
    go

    create table dbo.T1 (
    c1 int not null identity(1, 1),
    constraint PK_T1  primary key (c1)
    );
    go

    create table dbo.T2 (
    c1 int null,
    constraint FK_T2_T1 foreign key (c1) references dbo.T1(c1)
    );
    go

    insert into dbo.T1 default values;
    go

    insert into dbo.T2(c1) values(1);
    go

    insert into dbo.T2(c1) values(NULL);
    go

    insert into dbo.T2(c1) values(2);
    go

    select * from dbo.T1;
    go

    select * from dbo.T2;
    go

    drop table dbo.T2, dbo.T1;
    go


    AMB

    Monday, November 09, 2009 2:38 PM
  • Francesco

    is it possible for you to generate script of your table (source and target) and post?
    Monday, November 09, 2009 3:53 PM
  • Here below you can find the script of my table ( I have deleted the exetended properties,script was too long). Do you see something wrong?
    Thanks Francesco


    USE

     

    [FIN]

    GO

    /****** Object: Table [dbo].[PDCDIM] Script Date: 11/10/2009 10:20:12 ******/

    SET

     

    ANSI_NULLS ON

    GO

    SET

     

    QUOTED_IDENTIFIER ON

    GO

    CREATE

     

    TABLE [dbo].[PDCDIM](

    [CODPDC] [nvarchar]

    (14) NULL,

    [CODPAD] [nvarchar]

    (14) NULL,

    [codmas] [smallint]

    NOT NULL,

    [codcon] [smallint]

    NOT NULL,

    [codsot] [int]

    NOT NULL,

    [descri] [nvarchar]

    (30) NULL,

    [FLGANN] [nvarchar]

    (10) NULL,

    [NUMLIV] [smallint]

    NULL,

    [CODP01] [nvarchar]

    (255) NULL,

    [CODP02] [nvarchar]

    (255) NULL,

    [CODP03] [nvarchar]

    (255) NULL,

    [CODP04] [nvarchar]

    (255) NULL,

    [CODP05] [nvarchar]

    (255) NULL,

    [CODR01] [nvarchar]

    (20) NULL,

    [CODR02] [nvarchar]

    (20) NULL,

    [CODR03] [nvarchar]

    (255) NULL,

    [CODR04] [nvarchar]

    (255) NULL,

    [CODR05] [nvarchar]

    (255) NULL,

     

    CONSTRAINT [aaaaaPDCDIM_PK] PRIMARY KEY NONCLUSTERED

    (

    [codmas]

    ASC,

    [codcon]

    ASC,

    [codsot]

    ASC

    )

     

    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    )

     

    ON [PRIMARY]

    GO

     

     

    GO

    ALTER

     

    TABLE [dbo].[PDCDIM] WITH NOCHECK ADD CONSTRAINT [FK_PDCDIM_PDCR01] FOREIGN KEY([CODR02])

    REFERENCES

     

    [dbo].[PDCR01] ([CODR01])

    GO

    ALTER

     

    TABLE [dbo].[PDCDIM] NOCHECK CONSTRAINT [FK_PDCDIM_PDCR01]

    GO

    ALTER

     

    TABLE [dbo].[PDCDIM] WITH NOCHECK ADD CONSTRAINT [PDCDIM_FK00] FOREIGN KEY([CODR01])

    REFERENCES

     

    [dbo].[PDCR01] ([CODR01])

    GO

    ALTER

     

    TABLE [dbo].[PDCDIM] NOCHECK CONSTRAINT [PDCDIM_FK00]

    GO

     

    Tuesday, November 10, 2009 9:28 AM
  • Hi,
    I have try to run your script, but it gives me errors:
    In your sql server it runs correctly?
    Thanks Francesco

    (1 row(s) affected)

    (1 row(s) affected)

    (1 row(s) affected)

    Msg 547, Level 16, State 0, Line 2

    The INSERT statement conflicted with the FOREIGN KEY constraint "FK_T2_T1". The conflict occurred in database "tempdb", table "dbo.T1", column 'c1'.

    The statement has been terminated.

    (1 row(s) affected)

    (2 row(s) affected)

    Tuesday, November 10, 2009 9:29 AM