不允许我将外键添加到SQL Server中的表中 [英] Won't let me add a Foreign Key into a table in SQL Server

问题描述

我正在整理数据库并尝试对所有依赖项进行整理，而在向表中添加外键时，我一直遇到错误.我已经检查了约束，似乎无法发现任何约束，所以我只是想知道问题出在哪里.

I am tidying up a database and trying to sort all the dependencies out and I keep running into an error when it comes to adding a Foreign Key into a table. I have checked for constraints and can't seem to discover any so I am just wondering where the issue lies.

患者"表已成功保存约会"表-无法创建关系"FK_Appointments_PATIENTS".ALTER TABLE语句与FOREIGN KEY约束冲突"FK_Appointments_PATIENTS".数据库中发生了冲突"OEPD_PRO"，表"dbo.PATIENTS"，列"PatientNumber".

'PATIENTS' table saved successfully 'Appointments' table - Unable to create relationship 'FK_Appointments_PATIENTS'. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Appointments_PATIENTS". The conflict occurred in database "OEPD_PRO", table "dbo.PATIENTS", column 'PatientNumber'.

这是当我尝试保存表时不断加错的错误."PatientNumber"是"PATIENTS"表中的主键，我正尝试将其作为外键添加到"Appointments"表中.

This is the error which keeps flagging up when I try to save the table. 'PatientNumber' is the Primary Key in the 'PATIENTS' table and I'm trying to add it as a foreign key into the 'Appointments' table.

ALTER TABLE Appointments
ADD CONSTRAINT FK_PatientAppointments
FOREIGN KEY (PatientNumber) REFERENCES PATIENTS(PatientNumber);

我非常感谢您提供的任何帮助/建议.

I'm very grateful for any help/advice given.

谢谢，KB

推荐答案

检查约会中是否有无效的 PatientNumber s

Check Appointments for invalid PatientNumbers

select *
from Appointments a 
where not exists (
  select 1
  from Patients p
  where a.PatientNumber = p.PatientNumber
  )

如果需要删除它们，可以像这样删除它们:

If they need to be removed you can delete them like so:

delete a
from Appointments a 
where not exists (
  select 1
  from Patients p
  where a.PatientNumber = p.PatientNumber
  )

然后尝试添加外键.

这篇关于不允许我将外键添加到SQL Server中的表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！