MySQL: how can I see ALL constraints on a table?

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

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!

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

Solution 2:

The simplest way to see the explanation of a current table and its constraints is to use:

SHOW CREATE TABLE mytable;

This will show you exactly what SQL would be entered to define the table structure in its current form.

Solution 3:

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'