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