如何检查是否每个主键值都被引用为另一个表中的外键 [英] How to check if every primary key value is being referenced as foreign key in another table

问题描述

我有两个表tb1和tb2. tb1中的id是pk，在tb2中称为fk. 我想知道tb1是否具有ID值分别为1,2,3,4,5，tb2具有1,2,3,4的fk_values，但没有pk 5时如何找到它.

I have two table tb1 and tb2 . id in tb1 is pk and referenced as fk in tb2. I want to know if tb1 has id values as 1,2,3,4,5 and tb2 has fk_values for 1,2,3,4 but doesn't have for pk 5 how can i find this.

tb1
------
id   
------
1    
------
2   
------
3    
------
4
------
while tb2 

fk_id
-------
1
--
1
--
2
--
3
--
3
--

但是表2没有4的值，那么我如何在这里找出4的值.

but table 2 doesn't have values for 4 then how can i find out the 4 value here .

要使用的数据库是mysql.

Database to be used is mysql.

推荐答案

要查找tb1中的内容，而不是tb2中的内容，请执行以下操作:

To find what's in tb1 but not in tb2 do this:

SELECT tb1.* FROM tb1 LEFT JOIN tb2 ON tb1.id = tb2.fk_id WHERE tb2.fk_id IS NULL

以另一种方式(在tb2中而不在tb1中)进行此操作，在这种情况下不能这样做，因为有一个外键，但是尽管如此，以后您只需切换两个表可能对您有用

To do it the other way (in tb2 but not in tb1 ), which cannot be in this case because, there is a foreign key, but none the less it might be useful for you later simply switch the two tables

SELECT tb2.* FROM tb2 LEFT JOIN tb1 ON tb1.id = tb2.fk_id WHERE tb1.id IS NULL

这篇关于如何检查是否每个主键值都被引用为另一个表中的外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！