sql - 从 SQL Server 表的查询中提取外键约束和引用列
问题描述
我使用以下查询列出所有表约束,这在提取更改输出表所需的约束(添加主键、唯一约束)时效果很好。
select table_view,
object_type,
constraint_type,
constraint_name,
details
from (
select schema_name(t.schema_id) + '.' + t.[name] as table_view,
case when t.[type] = 'U' then 'Table'
when t.[type] = 'V' then 'View'
end as [object_type],
case when c.[type] = 'PK' then 'Primary key'
when c.[type] = 'UQ' then 'Unique constraint'
when i.[type] = 1 then 'Unique clustered index'
when i.type = 2 then 'Unique index'
end as constraint_type,
isnull(c.[name], i.[name]) as constraint_name,
substring(column_names, 1, len(column_names)-1) as [details]
from sys.objects t
left outer join sys.indexes i
on t.object_id = i.object_id
left outer join sys.key_constraints c
on i.object_id = c.parent_object_id
and i.index_id = c.unique_index_id
cross apply (select col.[name] + ', '
from sys.index_columns ic
inner join sys.columns col
on ic.object_id = col.object_id
and ic.column_id = col.column_id
where ic.object_id = t.object_id
and ic.index_id = i.index_id
order by col.column_id
for xml path ('') ) D (column_names)
where is_unique = 1
and t.is_ms_shipped <> 1
union all
select schema_name(fk_tab.schema_id) + '.' + fk_tab.name as foreign_table,
'Table',
'Foreign key',
fk.name as fk_constraint_name,
schema_name(pk_tab.schema_id) + '.' + pk_tab.name
from sys.foreign_keys fk
inner join sys.tables fk_tab
on fk_tab.object_id = fk.parent_object_id
inner join sys.tables pk_tab
on pk_tab.object_id = fk.referenced_object_id
inner join sys.foreign_key_columns fk_cols
on fk_cols.constraint_object_id = fk.object_id
union all
select schema_name(t.schema_id) + '.' + t.[name],
'Table',
'Check constraint',
con.[name] as constraint_name,
con.[definition]
from sys.check_constraints con
left outer join sys.objects t
on con.parent_object_id = t.object_id
left outer join sys.all_columns col
on con.parent_column_id = col.column_id
and con.parent_object_id = col.object_id
union all
select schema_name(t.schema_id) + '.' + t.[name],
'Table',
'Default constraint',
con.[name],
col.[name] + ' = ' + con.[definition]
from sys.default_constraints con
left outer join sys.objects t
on con.parent_object_id = t.object_id
left outer join sys.all_columns col
on con.parent_column_id = col.column_id
and con.parent_object_id = col.object_id) a
order by table_view, constraint_type, constraint_name
https://dataedo.com/kb/query/sql-server/list-all-table-constraints
但是,在外键作为外键约束(列)并且需要其引用列的情况下,它不起作用。
ALTER TABLE row16.table_view
ADD CONSTRAINT row16.contraint_name
FOREIGN KEY (**row16....**) REFERENCES row16.details(**row16....**);
|| actual value
ALTER TABLE DBO.AREA
ADD CONSTRAINT FK_AREA_DOM_AIRLINE
FOREIGN KEY (**should be 'airline_cd'**) REFERENCES DBO.DOM_AIRLINE(**should be 'cd'**);
我想使用以下语句添加外部约束,但我不知道如何在这里定义外键约束及其引用列..
我觉得可能需要对表约束查询进行一些更改,但我最近才开始学习 SQL,所以我还不熟悉整个概念......
如果您需要更多信息,请告诉我。感谢您的所有帮助!
解决方案
推荐阅读
- php - Woocommerce产品自定义字段保存功能与保存时订单附加费冲突
- c# - 覆盖 Application.persistentDataPath 的问题
- zabbix - 未找到 Zabbix 5.0 外部脚本
- c# - 如何分解此代码确实写入 csv 文件
- git - 是否可以使用 API 从 github 模板创建新的 github 存储库以进行组织?
- javascript - 为什么 JS 解构赋值与数字一起工作
- protocols - 是否有使用 42 位的 NEC IR 协议的变体?
- node.js - Mongodb 仅在存在时查询
- java - 使用 get 查询获取数据时出现 SQLSyntaxErrorException ORA-00933
- html - HTML选择未从键盘输入中选择正确的选项