如何在 MySQL 中显示表的唯一约束？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1836502/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I show unique constraints of a table in MySQL?
提问by TIMEX
I created them, but I forgot which ones they are.
我创造了它们，但我忘记了它们是哪个。
I just want to
我只想
- show them.
- remove all the constraints on a table.
- 让他们看。
- 删除表上的所有约束。
采纳答案by Paul Creasey
select distinct CONSTRAINT_NAME
from information_schema.TABLE_CONSTRAINTS
where CONSTRAINT_SCHEMA = 'mysql'
回答by Dave
select distinct CONSTRAINT_NAME
from information_schema.TABLE_CONSTRAINTS
where table_name = 'table_name' and constraint_type = 'UNIQUE';
回答by user12345
This doesn't produce elegant output but is easy to remember:
这不会产生优雅的输出，但很容易记住：
SHOW CREATE TABLE table_name;
回答by adc
The OP asked for a single table, which this will do.
OP 要求提供一张桌子，这可以做到。
In addition, removing the last whereclause will show all columns for a database which are protected by unique constraints:
此外，删除最后一个where子句将显示受唯一约束保护的数据库的所有列：
SELECT
CONSTRAINT_NAME,
TABLE_NAME,
COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE
CONSTRAINT_NAME LIKE 'UNIQ%'
AND TABLE_SCHEMA = 'your_database_name'
AND TABLE_NAME = 'your_table_name';
Unfortunately mysql doesn't facilitate the removal of indexes based on a query result. You could execute the output of the following query to drop all unique columns in 2 queries:
不幸的是，mysql 不方便根据查询结果删除索引。您可以执行以下查询的输出以删除 2 个查询中的所有唯一列：
SELECT CONCAT(
'ALTER TABLE ',
TABLE_NAME,
' DROP INDEX ',
CONSTRAINT_NAME,
'; -- drops ',
COLUMN_NAME,
' constraint'
)
FROM information_schema.KEY_COLUMN_USAGE
WHERE
CONSTRAINT_NAME LIKE 'UNIQ%'
AND TABLE_SCHEMA = 'your_database_name';
