如何查找特定行的外键依赖关系?
如果我有一个表,表A:如何查找特定行的外键依赖关系?
Id
1
2
3
...
和两个其他表:
表B:
Id, TableAId
1 1
2 1
表C:
Id, TableAId
1, 1
2, 2
凡TableAId是一个FK关系与TableA.Id。
如何确定TableA Id 1有三行指向它?而表A,Id 2有一行指向它?更具体地说,我如何确定这些行是什么? (他们的表名和Id)
您可以使用INFORMATION_SCHEMA
视图生成选择语句以显示有问题的行。我只是针对问题中提供的表格进行了测试,但可以在密钥为多列的情况下进行扩展。
declare @table_schema nvarchar(50) = 'dbo',
@table_name nvarchar(50) = 'TableA',
@id int = 1
select fk_col.TABLE_SCHEMA, fk_col.TABLE_NAME, fk_col.COLUMN_NAME,
'select * from ' + fk_col.TABLE_SCHEMA + '.' + fk_col.TABLE_NAME + ' t1 '
+ ' inner join ' + @table_schema + '.' + @table_name + ' t2 '
+ ' on t1.' + fk_col.COLUMN_NAME + ' = t2.' + pk_col.COLUMN_NAME
+ ' where t2.' + pk_col.COLUMN_NAME + ' = ' + cast(@id as nvarchar)
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE pk_col
on pk.CONSTRAINT_SCHEMA = pk_col.CONSTRAINT_SCHEMA
and pk.CONSTRAINT_NAME = pk_col.CONSTRAINT_NAME
join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS fk
on pk.CONSTRAINT_SCHEMA = fk.UNIQUE_CONSTRAINT_SCHEMA
and pk.CONSTRAINT_NAME = fk.UNIQUE_CONSTRAINT_NAME
join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE fk_col
on fk_col.CONSTRAINT_SCHEMA = fk.CONSTRAINT_SCHEMA
and fk_col.CONSTRAINT_NAME = fk.CONSTRAINT_NAME
where pk.TABLE_SCHEMA = @table_schema
and pk.TABLE_NAME = @table_name
and pk.CONSTRAINT_TYPE = 'PRIMARY KEY'
产生的select语句:
select * from dbo.TableB t1 inner join dbo.TableA t2 on t1.TableAId = t2.Id where t2.Id = 1
select * from dbo.TableC t1 inner join dbo.TableA t2 on t1.TableAId = t2.Id where t2.Id = 1
和查询结果:
Id TableAId Id
----------- ----------- -----------
1 1 1
2 1 1
Id TableAId Id
----------- ----------- -----------
1 1 1
伟大的答案,thx :) – mrzepa
嗨,请你能给我相当的Mysql代码。 –
为了找出FK关系存在,您需要检查SQL Server中的sys
目录视图 - 是这样的:
SELECT *
FROM sys.foreign_keys
WHERE referenced_object_id = OBJECT_ID('TableA')
这将列出所有外键存在于TableA
的关系。
一旦你有了这些信息,在TableA
和其他任何涉及的表格之间的联系就非常简单了。
更新:一旦您知道例如TableB
和TableC
参考您的TableA
,你可以找到一个简单的depdendent行JOIN:
SELECT c.*
FROM dbo.TableC c
INNER JOIN dbo.TableA a ON a.ID = c.TableAID -- or whatever column joins the tables.....
WHERE....... -- possibly with a WHERE clause...
@KirkWoll:是的,我知道 - 但在数据级别的依赖只存在于架构上!如果你不知道什么**其他表引用'TableA',你有**没办法**找出**行**来自那些其他表引用行中'TableA' –
I * do *了解模式级别的依赖关系。但是这并不能告诉我如何找到数据依赖关系。我如何找到依赖*行*?实际上,我有大约20-30张桌子都指向这张桌子。创建这个庞大的连接是唯一的方法? –
@KirkWoll:如果您不知道哪些行可能是引用您在“TableA”中的行,您如何查找数据依赖关系?你需要**模式依赖关系**首先 - **然后**(只有然后)你可以寻找数据依赖关系.... –
我没有在此计算机上的SQL,所以我不能给你确切的代码,但在这里就是这样,你应该走。请注意,我将使用SQL Server术语。
我没有办法做到这一点没有动态sql,至少在SQL Server中。
- 用列
FK_TBL_NM
,FK_CLMN_VAL
创建临时表#t。 -
它不应该是很难得到所有TableA的的PK的FK关系:
SELECT * FROM sys.foreign_keys WHERE referenced_object_id = OBJECT_ID('TableA')
使用游标来遍历通过这个查询和每个生成的结果,并执行动态SQL将加入
TableA
,并从光标中检索表并返回FK_TBL_NM
(TableB,TableC,...)和fk列的值。插入结果存入#T(这是棘手的动态SQL结果到表,但做计算器研究)
现在你有一个包含一行在TableB
每一行表,TableC
,...
我知道这是可行的,因为我在几天前的工作中编写了类似逻辑的代码。
请注意,您应该可以使您的代码与pk/fk一起使用,并且多于一列。也有不同的数据类型的列。它使事情变得复杂一点,但它是可能的。
上面列出的每一步都不难实现。但是,如果您遇到任何困难,请在计算器上搜索:)
你的目标并不完全清楚。您是否想要查找元数据以识别表之间的外键实数船,或者是否希望TSql查询找到已知外键关系的数据? – user957902
我对元数据不感兴趣。在我的示例中,我展示了如何确定对另一个特定行具有依赖关系的表和特定行。我想在不写一个(可能)巨大的select语句的情况下获得这些信息,这个语句将每个可能的表连接到目标表。 –