INSERT与外键冲突 - SQL Server错误？-Java 学习之路

更新：针对SQL Server 2008运行时不会发生此问题 . 因此，这对SQL Server 2000来说是奇怪的（或错误的） .

我尝试在SQL Server 2000上进行简单的插入：

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

我明白了

INSERT语句与COLUMN FOREIGN KEY约束'FK569ABB5045EE0940'冲突 . 冲突发生在数据库'Orders'，表'Addresses'，列'Id'中 .

我很清楚这意味着什么，但我无法理解为什么会发生冲突 - 请注意我从地址表中插入ID，所以它们确实存在！为什么SQL Server不能在Addresses表中的外键端找到它们？我应该傻傻的

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

或者是什么？

更多信息：ID是GUID，数据来自旧数据库（导入） . 首先，我填充地址，然后尝试插入UserAddresses . 如果我做SELECT TOP 100 ...它的工作原理...所以这是一个记录的问题，但我不明白为什么会发生 .

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

更新：我刚刚完成了这个没有错误（查询批处理完成）：

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

我想知道为什么INSERT会在foreach光标工作时失败...

更新：oops，光标完成后，INSERT也可以工作 . 但它永远不会独立运作 . 这是我做的：

运行导入脚本，以便填充“地址”表
手动运行INSERT - 失败
手动运行CURSOR - 它有效
从UserAddresses删除
手动运行INSERT - 它现在可以运行了

这是一个魔术还是我是一个完全白痴失踪的东西？

更新：如果我这样做

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

它也有效 . 我认为这是SQL Server 2000中的一个错误，尽管“永远不会责怪编译器”规则 .

3 回答

-1

更新 - “哈里”架构

gbn评论说这可能是架构问题 . 我更新了我的原始代码示例，并且能够得到（几乎*）确切的错误 .

（*请注意，我在2008年运行此操作，OP在2000上运行.SQL 2008架构 - 在错误消息中限定表 . ）

更新代码 - “哈里”架构

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

结果

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

原帖

queen3，这是我认为你正在尝试的一个完整的工作示例 . 我试图使它与SQL 2000兼容，但我只有2005和2008可用 .

请创建一个新数据库并运行此脚本 . 如果它没有重复你想要做的事情，请解释或只是发布修改后的代码 .

这个脚本按原样运行，但我确信有些东西与你的应用程序不同 .

抢

代码

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

回复于 2024-04-24T03:09:46+08:00

0
随机思考......

你使用什么凭据，ORM使用什么，以及什么模式？

例如，表和FK实际上使用“bob”模式
- bob.Addresses
- bob.Users
- bob.UserAddresses
但由于前SQL 2005用户/架构的东西，你处于“哈里”模式......
```
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
```
不止一次，我很享受测试人员和开发人员因为缺乏合格模式而获得不同结果的奇观......
回复于 2024-04-24T03:09:46+08:00
1

检查UserAddresses表 . 也许有人在表上定义了一个（BAD！）触发器，它以某种方式对Addresses或UserAddresses表执行一些Evil .

回复于 2024-04-24T03:09:46+08:00

INSERT与外键冲突 - SQL Server错误？

3 回答

更新 - “哈里”架构

更新代码 - “哈里”架构

结果

原帖

代码

相关问题