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!

Could you please specify what is a result of the queries you run? * P.S What I've heard is that mysql does not support check constraints. — user194076, Feb 23 '11 at 18:09
You can try to query system tables directly, not Information Schema views — user194076, Feb 23 '11 at 18:12
Possible duplicate - https://stackoverflow.com/questions/3024981/list-of-constraints-from-mysql-database — Devesh Saini, Feb 20 '18 at 00:05

score 64 · Answer 1 · edited Sep 11 '13 at 14:23

64

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

edited Sep 11 '13 at 14:23

bummi

26,839
13
60
97

answered Sep 11 '13 at 14:18

RRM

2,385
26
40

4

As of March 2020, this query does not show any CHECK constraints that the table may have. – tronman Mar 09 '20 at 19:16

score 35 · Answer 2 · answered Apr 20 '16 at 17:12

35

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.

answered Apr 20 '16 at 17:12

John Foley

4,115
3
18
23

score 14 · Answer 3 · answered Mar 17 '13 at 20:59

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'

Is there anyway to display all database names in the table as well? — Dexter, Apr 25 '18 at 13:46

blackbishop · Answer 4 · 2019-03-11T08:41:47.820

12

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

edited Mar 11 '19 at 08:41

answered Aug 04 '14 at 10:49

blackbishop

26,760
8
50
69

score 3 · Answer 5 · answered Feb 23 '11 at 18:54

3

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

answered Feb 23 '11 at 18:54

sreimer

4,794
2
31
41

This has not worked for me (mysql 5.5.35-0ubuntu0.12.04.2): the comments field is blank, whereas R R Madhav's solution shows constraints returned as columns. – msanford Jul 11 '14 at 20:24

score 1 · Answer 6 · answered Dec 05 '16 at 13:44

1

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

answered Dec 05 '16 at 13:44

Karan Motwani

29
2

4

You may want to delete your answer since CHECK constraints are now supported. – tronman Mar 09 '20 at 19:12
* as of MySql 8 – SuperDJ Dec 02 '21 at 09:45

score 0 · Answer 7 · answered Sep 18 '15 at 14:11

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

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

7 Answers7

Linked