SQL - 如何从表中获取唯一键的列名

SQL - How to get the Unique Key's Column Name from Table

提问by eqiz

I know how to get the columns from a table using the following SQL statement:

我知道如何使用以下 SQL 语句从表中获取列：

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE (TABLE_NAME = 'MYTABLENAME')

But how do I just return what the UNIQUE Key's Column name?

但是我如何只返回 UNIQUE Key 的列名？

采纳答案by Donald Byrd

Something like this might work (untested):

像这样的事情可能有效（未经测试）：

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
WHERE TC.TABLE_NAME = 'MYTABLENAME'
AND TC.CONSTRAINT_TYPE = 'UNIQUE'

回答by Christian Hayter

select CCU.CONSTRAINT_NAME, CCU.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS as TC
inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as CCU
    on TC.CONSTRAINT_CATALOG = CCU.CONSTRAINT_CATALOG
    and TC.CONSTRAINT_SCHEMA = CCU.CONSTRAINT_SCHEMA
    and TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
where TC.CONSTRAINT_CATALOG = 'MyCatalogName'
and TC.CONSTRAINT_SCHEMA = 'MySchemaName'
and TC.TABLE_NAME = 'MyTableName'
and TC.CONSTRAINT_TYPE = 'UNIQUE'

Bear in mind that a table may have multiple unique constraints, each containing multiple columns. You will need to apply some additional logic to select the right one.

请记住，一个表可能有多个唯一约束，每个约束包含多个列。您需要应用一些额外的逻辑来选择正确的逻辑。

UPDATE- based on other comments...

更新- 基于其他评论...

The above query will find all UNIQUEkey constraints. However, it will not find PRIMARY KEYconstraints, or UNIQUEindexes that were created outside a UNIQUEkey constraint.

上面的查询将找到所有UNIQUE关键约束。但是，它不会找到PRIMARY KEY约束或UNIQUE在UNIQUE键约束之外创建的索引。

To find the primary key, replace the last line with:

要查找主键，请将最后一行替换为：

and TC.CONSTRAINT_TYPE = 'PRIMARY KEY'

回答by DaveShaw

Something like this:

像这样的东西：

Select col.name From 
sys.objects obj 
Join sys.columns col on col.[object_id] = obj.[object_id]
Join sys.index_columns idx_cols on idx_cols.[column_id] = col.[column_id] and idx_cols.[object_id] = col.[object_id]
Join sys.indexes idx on idx_cols.[index_id] = idx.[index_id] and idx.[object_id] = col.[object_id]
where obj.name = 'MYTABLENAME'
and idx.is_unique = 1

回答by eqiz

The two that I found to work are the following, the 2nd one was from the original poster but without the TC.CONSTRAINT_TYPE = 'UNIQUE'. That condition wasn't working

我发现可以工作的两个如下，第二个来自原始海报，但没有 TC.CONSTRAINT_TYPE = 'UNIQUE'。那个条件不起作用

SELECT     col.name
FROM         sys.objects AS obj INNER JOIN
                      sys.columns AS col ON col.object_id = obj.object_id INNER JOIN
                      sys.index_columns AS idx_cols ON idx_cols.column_id = col.column_id AND idx_cols.object_id = col.object_id INNER JOIN
                      sys.indexes AS idx ON idx_cols.index_id = idx.index_id AND idx.object_id = col.object_id
WHERE     (obj.name = 'pluginUsers') AND (idx.is_unique = 1)

and also

并且

SELECT     CCU.CONSTRAINT_NAME, CCU.COLUMN_NAME
FROM         INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC INNER JOIN
                      INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CCU ON TC.CONSTRAINT_CATALOG = CCU.CONSTRAINT_CATALOG AND 
                      TC.CONSTRAINT_SCHEMA = CCU.CONSTRAINT_SCHEMA AND TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
WHERE     (TC.TABLE_NAME = 'pluginUsers')

Thank you all for your posts

谢谢大家的帖子

回答by Brian Hooper

Wouldn't DESCRIBE TABLE_NAME; do the trick?

不会描述 TABLE_NAME；做的伎俩？

回答by Pankaj Kumar

SELECT * 
FROM mbiis.INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME='TABLE_NAME' AND CONSTRAINT_TYPE='UNIQUE';