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

Question

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!

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