在现有表上创建外键失败，但找不到孤立记录

问题描述：

虽然重构一个大的系统，我想创建两个已填充表之间的外键：

ALTER TABLE CMS_ARTRANS 
ADD CONSTRAINT FK_ARTRANS_PRACTITIONER_ID 
FOREIGN KEY (PRACTITIONER_ID) 
REFERENCES CMS_SOLICITORS (RECID);

这失败的消息：

违反外键约束“的”。违反表“CMS_SOLICITORS”上的FOREIGN KEY约束“PK_CMS_SOLICITORS”。外键参考目标不存在。

我有点期待在那里与参照完整性，这就是为什么我要在FK摆在首位的问题。所以我去寻找不匹配的记录：

SELECT 
    * 
FROM CMS_ARTRANS AR 
LEFT OUTER JOIN CMS_SOLICITORS S 
    ON (S.RECID = AR.PRACTITIONER_ID) 
WHERE (AR.PRACTITIONER_ID IS NOT NULL) AND (S.RECID IS NULL)

而且没有。 CMS_ARTRANS.PRACTITIONER_ID中有大量NULL。但是没有与CMS_SOLICITOR记录不匹配的非NULL值。

为什么火鸟不喜欢我的FK？

我不知道你的特定的DBMS，但您可能需要明确指出的的Fkey可以'NULL'，仍然是有效的。请参阅：http://*.com/questions/2366854/can-table-columns-with-a-foreign-key-be-null – aruisdante 2014-11-03 16:23:47

是否有'PRACTITIONER_ID'现有指数？这可能发生在索引损坏的情况下：查询使用索引并且看不到某些记录，而实际创建外键确实会看到它们。您可以通过删除索引或 - 假设这是一个整数字段 - 用'AR.PRACTITIONER_ID + 0'替换**出现的两个** AR.PRACTITIONER_ID'（这将强制查询不使用索引）。 – 2014-11-03 18:05:34

我在纠正外键字段中的值后不久就发生了这种情况。等待一段时间（让事务变得比最早的有趣事务更旧），并用gfix -sweep清理数据库似乎可以解决它。 – nater 2014-11-03 19:12:05

答

最常见的，这种情况发生时，有对不符合主键之外的交易可见记录：也许你删除例如，所有有问题的记录，但他们仍然对其他事务可见。因此，解决方案要么等待比您的更早的交易关闭，要么迫使他们关闭。

在实践中，最简单的方法是使用gfix采取数据库脱机（如果你能负担得起）。

是的，我认为这是我的问题的原因。我没有想过并发用户/交易，因为它发生在数据库的开发副本上，我希望成为它的唯一用户。我现在怀疑通过在我的DB前端工具（IBExpert）中打开“隐形事务”来阻止我自己。重新启动DB服务器服务作为最残酷的措施，然后重试添加约束修复了问题。就像平常一样：简单易懂。谢谢大家。 – 2014-11-04 14:56:57

在现有表上创建外键失败，但找不到孤立记录

相关推荐