MySQL:如何查看表上的所有约束? [英] MySQL: how can I see ALL constraints on a table?

问题描述

我正在学习SQL，令我困扰的是，我似乎无法在表上找到所有约束.我用

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

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

并添加了一个约束

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

然后

show create table t2;

然后

select *
from information_schema.key_column_usage
where table_name='t2';

最后

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?

非常感谢！

推荐答案

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

这篇关于MySQL:如何查看表上的所有约束?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！