MySQL：如何在表上看到所有约束？

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!

非常感谢！

8 个解决方案

#1

select COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME, REFERENCED_TABLE_NAME
from information_schema.KEY_COLUMN_USAGE
where TABLE_NAME = 'table to be checked';

#2

MySQL does not suport check constraints. The SQL is parsed, accepted and then silently ignored without any message to the user.

MySQL不支持检查约束。 SQL被解析，接受，然后静默忽略，而不向用户发送任何消息。

As the check constraint is not created, you will not see it.

由于未创建检查约束，您将看不到它。

#3

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将以其当前形式定义表结构的输入内容。

#4

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'

#5

The foreign key constraints are listed in the Comment column of the output from the following command:

外键约束列在以下命令的输出的“注释”列中：

 SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';

#6

You could use a Select from information_schema.table_constraints like this :

你可以使用select from information_schema.table_constraints，如下所示：

mysql> select * from information_schema.table_constraints
-> where table_schema = schema()
-> and table_name = 'table_name';

#7

Unfortunately MySQL does not support SQL check constraints. When you define them in your query they are just ignored.

不幸的是，MySQL不支持SQL检查约束。在查询中定义它们时，它们将被忽略。

#8

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;
...

#1

select COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME, REFERENCED_TABLE_NAME
from information_schema.KEY_COLUMN_USAGE
where TABLE_NAME = 'table to be checked';

#2