views:

660

answers:

3

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] 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'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:

  1. Run the import script so that it populates Addresses table
  2. Manually run INSERT - it fails
  3. Manually run CURSOR - it works
  4. DELETE FROM UserAddresses
  5. 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.

A: 

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.

Dave Markle
The whole database is generated by ORM (NHibernate) just before I do import. It is completely innocent, even without data.
queen3
Trigger are good not bad, they are only bad if the developer is too uneducated to use them properly.
HLGEM
Triggers can be good, or they can be "bad". An example of a Bad Trigger would be one that modifies your UserAddresses or Addresses table in some evil way that is not transparent. That's what I meant.
Dave Markle
+1  A: 

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
Rob Garrison
If you find that it fails on SQL 2000 (which I suspect it won't), try it on SQL 2005 or 2008 also to see if there is a difference. I created and tested the script using 2008.
Rob Garrison
I can see why it fails for two tables with different users, but there's only one user in my DB. And it doesn't fail if I apply FK afterwards for me. As for your original test, it doesn't fail for me, and I can't test on 2005/8 because I don't have them installed. I'll post more details on what I do but I still think it's a problem with big-batch insert, because it works in 1-by-1 cursor, in TOP 1/100, and when FK is applied afterwards. So it's not a problem with the data or schema. Nevertheless, thanks for such a detailed investigation.
queen3
I've added more details on tables and how I populate Addresses. Not that I think it will help... Currently I'm fine with DROP/ADD CONSTRAINT so this question remains only for curiosity. Just a weird bug that I don't and would like to understand.
queen3
A: 

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...

gbn
Good point. I had "de-schema-fied" my test code. Check out the update to my post.
Rob Garrison