INSERT conflicted with foreign key - SQL Server bug?_问答_开发者

UPDATE: the issue does not happen when run against SQL Server 2008. So this is something strange (or wrong) with SQL Server 2000.

I try to do a simple insert on SQL Server 2000:

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

and I get this:

INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK569ABB5045EE0940'. The conflict occurred in database 'Orders', table 'Addresses', column 'Id'.

I'm well aware of what this means, but I can't understand why conflict happens - notice that I insert IDs from the Addresses table, so they DO exist! Why can't SQL Server find them on the foreign key end in the Addresses table? Should I do silly

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

or what?

Some more info: the IDs are GUIDs, data comes from the legacy DB (import). First I populate Addresses, then try to insert into UserAddresses. If I do SELECT TOP 100 ... it works... so it's a problem with some record but I can't understand why it happens.

CREATE TABLE [Addresses] (
    [Id] [uniqueidentifier] NOT NULL ,
     PRIMARY KEY  CLUSTERED ([Id])  ON [PRIMARY] ,
) ON [PRIMARY]
CREATE TABLE [Users] (
    [Id] [uniqueidentifier] N开发者_C百科OT 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've just done this without errors (query batch completed):

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 INSERT fails while foreach cursor works...

UPDATE: oops, after cursor completed, INSERT works, too. But it never works standalone. Here's what I do:

Run the import script so that it populates Addresses table
Manually run INSERT - it fails
Manually run CURSOR - it works
DELETE FROM UserAddresses
Manually run INSERT - it works now

Is it a magic or I'm 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 it's a bug in SQL Server 2000 despite the "never blame the compiler" rule.

Update - The "harry" Schema

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

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

Updated Code - The "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're attempting. I tried to make it SQL 2000-compatible, but I only have 2005 and 2008 available.

Please create a new database and run this script. If it does not duplicate what you're trying to do, please explain or just post modified code.

This script works as-is, but I'm sure there is something that is 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

Random thought...

What credentials are you using, what did ORM use, and what schema?

eg the tables and FKs are actually using "bob" schema

bob.Addresses
bob.Users
bob.UserAddresses

but because of pre-SQL 2005 user/schema stuff, you are in "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've enjoyed the spectacle of testers and developers getting different results because of lack of qualifying schema...

Check the UserAddresses table. Maybe someone has defined a (BAD!) trigger on the table that does some Evil to the Addresses or UserAddresses table somehow.