MySQL：如何查看表上的所有约束？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5094948/
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
MySQL: how can I see ALL constraints on a table?
提问by Alexander
I'm learning SQL and what bothers me, is that I seem unable to find ALL constraints on a table. I created the table with
我正在学习 SQL，让我烦恼的是我似乎无法在表上找到所有约束。我用
create table t2
(a integer not null primary key,
b integer not null, constraint c1 check(b>0),
constraint fk1 foreign key(a) references t1(a));
and added a constraint with
并添加了一个约束
alter table t2
add constraint c2 check (b<20);
I then tried to see ALL (four) constraints with
然后我尝试查看所有（四个）约束
show table status
from tenn #-->the name of my database
like 't2';
and then
进而
show create table t2;
and then
进而
select *
from information_schema.key_column_usage
where table_name='t2';
and finally
最后
select *
from information_schema.table_constraints
where table_name='t2';
But none of these shows all four constraints. Could anyone tell me how to see all of them?
但是这些都没有显示所有四个约束。谁能告诉我如何看到所有这些？
Thanks a lot!
非常感谢！
回答by RRM
select COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME, REFERENCED_TABLE_NAME
from information_schema.KEY_COLUMN_USAGE
where TABLE_NAME = 'table to be checked';
回答by John Foley
The simplest way to see the explanation of a current table and its constraints is to use:
查看当前表及其约束的解释的最简单方法是使用：
SHOW CREATE TABLE mytable;
SHOW CREATE TABLE mytable;
This will show you exactly what SQL would be entered to define the table structure in its current form.
这将准确显示将输入什么 SQL 来定义当前形式的表结构。
回答by Abhishek Gupta
You can use this:
你可以使用这个：
select
table_name,column_name,referenced_table_name,referenced_column_name
from
information_schema.key_column_usage
where
referenced_table_name is not null
and table_schema = 'my_database'
and table_name = 'my_table'
Or for better formatted output use this:
或者为了更好的格式化输出使用这个：
select
concat(table_name, '.', column_name) as 'foreign key',
concat(referenced_table_name, '.', referenced_column_name) as 'references'
from
information_schema.key_column_usage
where
referenced_table_name is not null
and table_schema = 'my_database'
and table_name = 'my_table'
回答by blackbishop
You could get it from information_schema.table_constraintslike this :
你可以从information_schema.table_constraints这样得到它：
SELECT *
FROM information_schema.table_constraints
WHERE table_schema = schema()
AND table_name = 'table_name';
回答by sreimer
The foreign key constraints are listed in the Comment column of the output from the following command:
外键约束列在以下命令输出的 Comment 列中：
SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';
回答by Karan Motwani
Unfortunately MySQL does not support SQL check constraints. When you define them in your query they are just ignored.
不幸的是，MySQL 不支持 SQL 检查约束。当您在查询中定义它们时，它们会被忽略。
回答by hawk8
Export the database table in SQL.
在 SQL 中导出数据库表。
If you have phpmyadmin, you can do so by visiting the "Export" tab. If you choose the "Custom" export method, be sure to select either "structure" or "structure and data" under the "Format-specific options" section.
如果您有 phpmyadmin，则可以通过访问“导出”选项卡来执行此操作。如果您选择“自定义”导出方法，请确保在“格式特定选项”部分下选择“结构”或“结构和数据”。
Sample .sql export snippet:
示例 .sql 导出片段：
--
-- Table structure for table `customers`
--
CREATE TABLE `customers` (
`username` varchar(50) NOT NULL,
`fullname` varchar(100) NOT NULL,
`postalcode` varchar(50) NOT NULL,
PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
...
