Foreign Key constraint failure and error message when inserting values

Question

Hopefully someone can help. I have created two tables Customer and Order as follows;

CREATE TABLE Customer
CustomerID int NOT NULL PRIMARY KEY
CustomerName varchar(25)

The other columns in Customer table are not relevant to my question, so I will not include them here. My CustomerID numbers are from 1 through to 15, all unique.

The second table I created is Orders as follows

CREATE TABLE Orders
OrderID smallint NOT NULL PRIMARY KEY
OrderDate date NOT NULL
CustomerID int FOREIGN KEY REFERENCES Customer (CustomerID);

My insert values is as follows

INSERT INTO Orders (OrderID, OrderDate, CustomerID)
VALUES
(1001, '2008-10-21', 1),
(1002, '2008-10-21', 8),
(1003, '2008-10-22', 15),
(1004, '2008-10-22', 5),
(1005, '2008-10-24', 3),
(1006, '2008-10-24', 2),
(1007, '2008-10-27', 11),
(1008, '2008-10-30', 12),
(1009, '2008-11-05', 4),
(1010, '2008-11-05', 1);

When I try to insert my values into the Order table, I get the following error message....

Msg 547, Level 16, State 0, Line 1.....The INSERT statement conflicted with the FOREIGN KEY constraint "FK__OrderT__Customer__2D27B809". The conflict occurred table "dbo.Customer", column 'CustomerID'. The statement has been terminated.

The numbers for CustomerID in my Order table, are (1; 1; 2; 3; 4; 5; 8; 11; 12 and 15). Therefore I have checked that all my CustomerID numbers in Order table are also in the Customer table.

So my questions are

1) Has the insert values failed because my CustomerID column in Customer table in NOT NULL and I in error made CustomerID column NULL in Order.

2) If the answer to the above question is yes, then is it possible for me to (a) drop the foreign key on the CustomerID column in Order (b) change the column to NOT NULL and (c) then add the foreign key constraint again to this column and then insert the values again?

It might be easier to drop and re-create the table Order. But I am curious if option 2 would work, re dropping and adding a foreign key on the same column.

Hopefully I am on the right track with why I think the error occurred, feel to correct me if I am wrong.

Thanks everyone Josie

Answer 1

1) It should be NOT NULL in both. However error is because you attempted to insert a CustomerId that is not in Customer table.

2) You can simply alter the table and make it NOT NULL (error was not that).

Sample:

CREATE TABLE Customer
  (
    CustomerID INT NOT NULL
                   PRIMARY KEY ,
    CustomerName VARCHAR(25)
  );

CREATE TABLE Orders
  (
    OrderID INT NOT NULL
                PRIMARY KEY ,
    OrderDate DATE NOT NULL ,
    CustomerID INT FOREIGN KEY REFERENCES Customer ( CustomerID )
  );

INSERT [Customer] ( [CustomerID], [CustomerName] )
VALUES  (   1, 'Customer 1' ),
(   2, 'Customer 2' ),
(   3, 'Customer 3' ),
(   4, 'Customer 4' ),
(   5, 'Customer 5' ),
(   6, 'Customer 6' );


INSERT [Orders] ( [OrderID], [OrderDate], [CustomerID] )
VALUES  
( 1, GETDATE(), 1 ),
( 2, GETDATE(), 2 ),
( 3, GETDATE(), 3 ),
( 4, GETDATE(), 4 ),
( 5, GETDATE(), 5 ),
( 6, GETDATE(), 6 );

INSERT [Orders] ( [OrderID], [OrderDate], [CustomerID] )
VALUES  ( 7, GETDATE(), 7 );

Last one would error, because Customer with CustomerID 7 doesn't exist.

Update: I later saw your sample insert. You can find the offending ID like this:

DECLARE @ids TABLE ( id INT );
INSERT  @ids ( [id] )
VALUES  ( 1 ),
        ( 8 ),
        ( 15 ),
        ( 5 ),
        ( 3 ),
        ( 2 ),
        ( 11 ),
        ( 12 ),
        ( 4 ),
        ( 1 );

SELECT  *
FROM    @ids AS [i]
WHERE   id NOT IN ( SELECT  CustomerID
                    FROM    [Customer] AS [c] );