我有以下脚本,这给了我一个错误:"插入语句与外键约束相冲突" fk_dbo.planshiftassignments_dbo.user_userid。该声明已终止。"
如您所见,在该子句中,我在dbo.user中检查USERID是否存在。错误的其他可能原因是什么?
更新:我也想知道从SELECT语句中的哪一行导致错误。关于调试此查询的任何建议将不胜感激。我正在使用MS SQL Server Management Studio。
CREATE TABLE [dbo].[PlanShiftAssignments] (
[PlanShiftId] [uniqueidentifier] NOT NULL,
[Status] [int] NOT NULL,
[UserId] [int],
CONSTRAINT [PK_dbo.PlanShiftAssignments] PRIMARY KEY ([PlanShiftId])
)
CREATE INDEX [IX_PlanShiftId] ON [dbo].[PlanShiftAssignments]([PlanShiftId])
CREATE INDEX [IX_UserId] ON [dbo].[PlanShiftAssignments]([UserId])
ALTER TABLE [dbo].[PlanShiftAssignments] ADD CONSTRAINT [FK_dbo.PlanShiftAssignments_dbo.PlanShifts_PlanShiftId] FOREIGN KEY ([PlanShiftId]) REFERENCES [dbo].[PlanShifts] ([Id])
ALTER TABLE [dbo].[PlanShiftAssignments] ADD CONSTRAINT [FK_dbo.PlanShiftAssignments_dbo.User_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[User] ([Id])
insert into dbo.PlanShiftAssignments
select ps.Id as PlanShiftId, ISNULL(ps.AssigneeId, psi.UserId) as UserId, ISNULL(psi.[Status], 1) as [Status] from dbo.PlanShifts ps
left join
dbo.PlanShiftInvitations psi
on ps.Id = psi.PlanShiftId
where (psi.UserId is not null and psi.UserId IN (select Id from dbo.[User]))
or (ps.AssigneeId is not null and ps.AssigneeId IN (select Id from dbo.[User]))
确保您始终在每个INSERT
语句中包含目标列列表。
insert into dbo.PlanShiftAssignments (
PlanShiftId,
UserId,
Status)
SELECT
ps.Id as PlanShiftId,
ISNULL(ps.AssigneeId, psi.UserId) as UserId,
ISNULL(psi.[Status], 1) as [Status]
...
您的表是用PlanShiftId, Status, UserId
订单创建的,当前SELECT
的列顺序为PlanShiftId, UserId, Status
,因此混乱。
您有一个奇怪的数据模型,如果UserId
和AssigneeId
尚未参考基础表中的User
。
无论如何,您的where
子句是
where (psi.UserId is not null and psi.UserId IN (select Id from dbo.[User])) or
(ps.AssigneeId is not null and ps.AssigneeId IN (select Id from dbo.[User]))
这留下了psi.UserId
匹配但ps.AssigneeId
不匹配的可能性。
要确保逻辑匹配,请使用与select
中相同的表达式:
where coalesce(ps.AssigneeId, psi.UserId) in (select Id from dbo.[User])
可能是因为您指定的一个或where子句中指定的事实,因此,受让人或用户ID在用户表中,而不是另一个,因此使FK约束无效。