时间戳

首页 > 解决方案 > 从主键获取外键 ID

sql - 从主键获取外键 ID

问题描述

我有一个问题，我不确定我的想法是否走得太远，但问题是我想查询 sql server 为我提供指定主键的外键 ID、列名和表。下面的查询给了我主键/外键约束、表等。

   SELECT 
    FK = OBJECT_NAME(fkc.constraint_object_id),
    Referencing_table = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.parent_object_id))
            + '.' + QUOTENAME(OBJECT_NAME(fkc.parent_object_id)),
    Referencing_col = QUOTENAME(pc.name),
    Referenced_table = QUOTENAME(OBJECT_SCHEMA_NAME(fkc.referenced_object_id)) 
            + '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)),
    Referenced_col = QUOTENAME(rc.name),
    PK = pk.name
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.columns AS pc
ON fkc.parent_object_id = pc.[object_id]
AND fkc.parent_column_id = pc.column_id
INNER JOIN sys.columns AS rc
ON fkc.referenced_column_id = rc.column_id
AND fkc.referenced_object_id = rc.[object_id]
INNER JOIN (SELECT i.name, fk.[object_id]
  FROM sys.indexes AS i
  INNER JOIN sys.foreign_keys AS fk
  ON i.[object_id] = fk.referenced_object_id
  AND i.index_id = fk.key_index_id
) AS pk
ON pk.[object_id] = fkc.constraint_object_id
ORDER BY Referencing_table, FK, fkc.constraint_column_id;

一个简单的表结构将是

create table test.ForeignTable1
(
  foreign_key_table_primary_key1 int primary key identity(1,1),
  randomdata1 varchar(200) not null
)

create table test.ForeignTable2
(
  foreign_key_table_primary_key2 int primary key identity(1,1),
  randomdata2 varchar(200) not null
)

create table test.PrimTable
(
   primary_key_column int primary key identity(1,1),
   ForeignTable1_data int foreign key references test.ForeignTable1(foreign_key_table_primary_key1) ,
   ForeignTable2_data int foreign key references test.ForeignTable2(foreign_key_table_primary_key2) 
)

sql 语句的期望输出将是返回，对于 PrimTable 中的特定主键，它将返回test.ForeignTable2和test.ForeignTable1中的所有关联 ID，并显示它们来自哪个表。

如果我提供 1，那么它将返回 ID (3,7,12) 用于test.ForeignTable1和 (6,4,458,88) 用于test.ForeignTable2

是否可以查询类似的内容？另外我想知道 sql server 如何在删除期间确定您在另一个表中有一个外键约束阻止您删除项目？

标签： sqlsql-servertsql

解决方案

SELECT
K_Table = FK.TABLE_NAME,
FK_Column = CU.COLUMN_NAME,
PK_Table = PK.TABLE_NAME,
PK_Column = PT.COLUMN_NAME,
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE PK.TABLE_NAME=@PK_TABLE_NAME 
---- optional:
ORDER BY
1,2,3,4

推荐阅读