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!

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

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.

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'

You could get it from information_schema.table_constraints like this :

SELECT * 
FROM   information_schema.table_constraints
WHERE  table_schema = schema()
AND    table_name = 'table_name';

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

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

Export the database table in 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.

Sample .sql export snippet:

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