INSERT encountered a foreign key - SQL Server error?

Question

INSERT encountered a foreign key - SQL Server error?

UPDATE: The problem does not occur when starting with SQL Server 2008. So this is something strange (or wrong) with SQL Server 2000.

I am trying to do a simple insert on SQL Server 2000:

INSERT INTO UserAddresses (UserId, AddressId)
  SELECT UserId, Id
  FROM Addresses

and I get the following:

The INSERT statement contradicted Constraint Constraints Column 'FK569ABB5045EE0940. The conflict occurred in the "Orders" database, "Addresses" table, "Identifier" column.

I am well aware of what this means, but I cannot figure out why the conflict is happening - note that I am inserting IDs from the Addresses table, so they exist! Why can't SQL Server find them at the end of the foreign key in the address table? Should i be doing stupid

SELECT * FROM Addresses 
WHERE Id NOT IN (SELECT Id FROM Addresses)

or what?

Additional info: IDs are GUIDs, data comes from legacy DB (import). First I fill in Addresses and then I try to insert into UserAddresses. If I do SELECT TOP 100 ... it works ... so it's a problem with some kind of record, but I can't figure out why this is happening.

CREATE TABLE [Addresses] (
    [Id] [uniqueidentifier] NOT NULL ,
     PRIMARY KEY  CLUSTERED ([Id])  ON [PRIMARY] ,
) ON [PRIMARY]
CREATE TABLE [Users] (
    [Id] [uniqueidentifier] NOT NULL ,
     PRIMARY KEY  CLUSTERED ([Id])  ON [PRIMARY] 
) ON [PRIMARY]
CREATE TABLE [UserAddresses] (
    [UserId] [uniqueidentifier] NOT NULL ,
    [AddressId] [uniqueidentifier] NOT NULL ,
    CONSTRAINT [FK569ABB5045EE0940] FOREIGN KEY 
    (
        [AddressId]
    ) REFERENCES [Addresses] (
        [Id]
    ),
    CONSTRAINT [UserAddressesToAddressFK] FOREIGN KEY 
    (
        [UserId]
    ) REFERENCES [Users] (
        [Id]
    )
) ON [PRIMARY]
ALTER TABLE Addresses ADD UserId UNIQUEIDENTIFIER
INSERT INTO Addresses (UserId, Id)
SELECT legacy_userid, legacy_single_useraddressid -- both are guids
FROM LegacyUsers INNER JOIN LegacyAddresses

UPDATE: I just did this with no error (request processing finished):

DECLARE c CURSOR FOR SELECT UserId, Id FROM Addresses
OPEN c
DECLARE @uid UNIQUEIDENTIFIER, @aid UNIQUEIDENTIFIER
FETCH NEXT FROM c INTO @uid, @aid
WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT @aid
   INSERT INTO UserAddresses (UserId, AddressId)
   VALUES (@uid, @aid)
FETCH NEXT FROM c INTO @uid, @aid
END
CLOSE c
DEALLOCATE c

I wonder why the INSERT fails while the foreach cursor is running ...

UPDATE: oops, after the cursor completes, INSERT also works. But it never works autonomously. That's what I'm doing:

Run the import script so that it populates the address table
Manually run INSERT - it doesn't work
Manually run CURSOR - it works
REMOVE FROM userAddresses
Manually run INSERT - now it works

Is this magic, or am I a complete idiot missing something?

UPDATE: if i do

ALTER TABLE UserAddresses DROP CONSTRAINT FK569ABB5045EE0940

INSERT INTO UserAddresses (UserId, AddressId)
SELECT UserId, Id
FROM Addresses

    alter table UserAddresses 
        add constraint FK569ABB5045EE0940
        foreign key (AddressId) 
        references Addresses

it also works. I think this is a bug in SQL Server 2000, despite the "never blame the compiler" rule.

+2

sql sql-server sql-server-2000

queen3 12 oct. '09 at 9:43

source to share

3 answers

Rob garrison · Answer 1 · 2009-10-12T17:45:36+0000

Update - "harry" schema

gbn commented that it might be a schema issue. I updated my source code example and was able to get (almost *) the exact error.

(* Note that I am running this in 2008 and the OP is running on 2000. SQL 2008 schema - qualifies the table in the error message.)

Updated code - "harry" schema

SET NOCOUNT ON
GO
--<< ========================== DROPS ==========================
IF OBJECT_ID('tempdb..#UserGUIDs') IS NOT NULL
    DROP TABLE #UserGUIDs
GO
IF OBJECT_ID('tempdb..#AddressGUIDs') IS NOT NULL
    DROP TABLE #AddressGUIDs
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE [id] = OBJECT_ID('UserAddresses'))
    DROP TABLE [UserAddresses]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE [id] = OBJECT_ID('Users'))
    DROP TABLE [Users]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE [id] = OBJECT_ID('dbo.Addresses'))
    DROP TABLE dbo.[Addresses]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE [id] = OBJECT_ID('harry.Addresses'))
    DROP TABLE harry.[Addresses]
GO

--<< ========================== TABLES ==========================
--<< Users
CREATE TABLE [Users] (
    [Id]        uniqueidentifier  NOT NULL DEFAULT NEWID()  PRIMARY KEY,
    [UserName]  varchar(10)       NOT NULL
) ON [PRIMARY]
GO

--<< Addresses
CREATE TABLE harry.[Addresses] (
    [Id]        uniqueidentifier  NOT NULL DEFAULT NEWID()  PRIMARY KEY,
    [Address1]  varchar(20)       NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE dbo.[Addresses] (
    [Id]        uniqueidentifier  NOT NULL DEFAULT NEWID()  PRIMARY KEY,
    [Address1]  varchar(20)       NOT NULL
) ON [PRIMARY]
GO

--<< UserAddresses
CREATE TABLE [UserAddresses] (
    [UserId]    uniqueidentifier NOT NULL,
    [AddressId] uniqueidentifier NOT NULL,
    CONSTRAINT [FK569ABB5045EE0940]       FOREIGN KEY ([AddressId]) REFERENCES [Addresses] ([Id]),
    CONSTRAINT [UserAddressesToAddressFK] FOREIGN KEY ([UserId])    REFERENCES [Users] ([Id])
) ON [PRIMARY]
GO

--<< ========================== DATA ==========================
--<< Populate Users
CREATE TABLE #UserGUIDs ([UserId] uniqueidentifier)
GO
INSERT INTO [Users] ([UserName]) VALUES ('UserName1')
INSERT INTO [Users] ([UserName]) VALUES ('UserName2')
INSERT INTO [Users] ([UserName]) VALUES ('UserName3')
INSERT INTO [Users] ([UserName]) VALUES ('UserName4')
GO
INSERT INTO #UserGUIDs ([UserId]) SELECT [Id] FROM [Users]
GO

--<< Populate Addresses
CREATE TABLE #AddressGUIDs ([AddressId] uniqueidentifier)
GO
INSERT INTO harry.[Addresses] ([Address1]) VALUES ('1234 First Street')
INSERT INTO harry.[Addresses] ([Address1]) VALUES ('2345 Second Street')
INSERT INTO harry.[Addresses] ([Address1]) VALUES ('3456 Third Street')
INSERT INTO harry.[Addresses] ([Address1]) VALUES ('4567 Fourth Street')
GO
INSERT INTO #AddressGUIDs ([AddressId]) SELECT [Id] FROM harry.[Addresses]
GO

PRINT 'Users'
SELECT * FROM [Users]
PRINT 'Addresses'
SELECT * FROM harry.[Addresses]
GO

--<< ========================== TEST ==========================
--<< Populate UserAddresses
INSERT INTO UserAddresses (UserId, AddressId)
SELECT
    u.Id, -- UserID
    a.Id  -- AddressID
FROM harry.Addresses   AS a
CROSS JOIN Users AS u
GO

PRINT 'UserAddresses'
SELECT * FROM [UserAddresses]
GO

Result

Msg 547, Level 16, State 0, Line 4
The INSERT statement conflicted with the FOREIGN KEY constraint "FK569ABB5045EE0940". The conflict occurred in database "RGTest1", table "dbo.Addresses", column 'Id'.

Original post

queen3, here is a complete working example of what I think you are trying. I tried to make it SQL 2000 compliant, but I only have 2005 and 2008.

Create a new database and run this script. If it doesn't duplicate what you are trying to do, please explain or just post the modified code.

This script works as is, but I'm sure there is something different from your application.

Rob

code

SET NOCOUNT ON
GO
--<< ========================== DROPS ==========================
IF OBJECT_ID('tempdb..#UserGUIDs') IS NOT NULL
    DROP TABLE #UserGUIDs
GO
IF OBJECT_ID('tempdb..#AddressGUIDs') IS NOT NULL
    DROP TABLE #AddressGUIDs
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE [id] = OBJECT_ID('UserAddresses'))
    DROP TABLE [UserAddresses]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE [id] = OBJECT_ID('Users'))
    DROP TABLE [Users]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE [id] = OBJECT_ID('Addresses'))
    DROP TABLE [Addresses]
GO

--<< ========================== TABLES ==========================
--<< Users
CREATE TABLE [Users] (
    [Id]        uniqueidentifier  NOT NULL DEFAULT NEWID()  PRIMARY KEY,
    [UserName]  varchar(10)       NOT NULL
) ON [PRIMARY]
GO

--<< Addresses
CREATE TABLE [Addresses] (
    [Id]        uniqueidentifier  NOT NULL DEFAULT NEWID()  PRIMARY KEY,
    [Address1]  varchar(20)       NOT NULL
) ON [PRIMARY]
GO

--<< UserAddresses
CREATE TABLE [UserAddresses] (
    [UserId]    uniqueidentifier NOT NULL,
    [AddressId] uniqueidentifier NOT NULL,
    CONSTRAINT [FK569ABB5045EE0940]       FOREIGN KEY ([AddressId]) REFERENCES [Addresses] ([Id]),
    CONSTRAINT [UserAddressesToAddressFK] FOREIGN KEY ([UserId])    REFERENCES [Users] ([Id])
) ON [PRIMARY]
GO

--<< ========================== DATA ==========================
--<< Populate Users
CREATE TABLE #UserGUIDs ([UserId] uniqueidentifier)
GO
INSERT INTO [Users] ([UserName]) VALUES ('UserName1')
INSERT INTO [Users] ([UserName]) VALUES ('UserName2')
INSERT INTO [Users] ([UserName]) VALUES ('UserName3')
INSERT INTO [Users] ([UserName]) VALUES ('UserName4')
GO
INSERT INTO #UserGUIDs ([UserId]) SELECT [Id] FROM [Users]
GO

--<< Populate Addresses
CREATE TABLE #AddressGUIDs ([AddressId] uniqueidentifier)
GO
INSERT INTO [Addresses] ([Address1]) VALUES ('1234 First Street')
INSERT INTO [Addresses] ([Address1]) VALUES ('2345 Second Street')
INSERT INTO [Addresses] ([Address1]) VALUES ('3456 Third Street')
INSERT INTO [Addresses] ([Address1]) VALUES ('4567 Fourth Street')
GO
INSERT INTO #AddressGUIDs ([AddressId]) SELECT [Id] FROM [Addresses]
GO

PRINT 'Users'
SELECT * FROM [Users]
PRINT 'Addresses'
SELECT * FROM [Addresses]
GO

--<< ========================== TEST ==========================
--<< Populate UserAddresses
INSERT INTO UserAddresses (UserId, AddressId)
SELECT
    u.Id, -- UserID
    a.Id  -- AddressID
FROM Addresses   AS a
CROSS JOIN Users AS u
GO

PRINT 'UserAddresses'
SELECT * FROM [UserAddresses]
GO

gbn · Answer 2 · 2009-10-12T17:56:00+0000

Random thoughts ...

What credentials are you using, what ORM were you using, and what schema?

for example tables and FK actually use the "bob" schema

bob.Addresses
bob.Users
bob.UserAddresses

but because the user / schema is pre-SQL 2005, you are in the "harry" schema ...

INSERT INTO UserAddresses (UserId, AddressId)
  SELECT UserId, Id
  FROM Addresses

-- is actually
INSERT INTO harry.UserAddresses (UserId, AddressId)
  SELECT UserId, Id
  FROM bob.Addresses
-- or
INSERT INTO bob.UserAddresses (UserId, AddressId)
  SELECT UserId, Id
  FROM harry.Addresses

More than once I have enjoyed the spectacle of testers and developers getting different results due to the lack of a qualifying circuit ...

Dave markle · Answer 3 · 2009-10-12T10:09:05+0000

Check the UserAddresses table. Maybe someone has defined a trigger (BAD!) On the table that somehow turns some Evil into the Addresses or UserAddresses table.

INSERT encountered a foreign key - SQL Server error?

Update - "harry" schema

Updated code - "harry" schema

Result

Original post

code

More articles: