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 66 · Answer 1 · edited Sep 11 '13 at 14:23

66

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

27,123
14
62
101

answered Sep 11 '13 at 14:18

RRM

2,495
29
46

5

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 39 · Answer 2 · answered Apr 20 '16 at 17:12

39

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,373
3
21
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

13

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

30,945
11
55
76

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,913
2
33
43

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

score 0 · Answer 8 · answered Nov 02 '22 at 16:52

Unfortunately, all of the answers here (except for SHOW CREATE TABLE, which shows many details of the table) do not return the CHECK constraint. The following query will return the CHECK Constraints on a table:

mysql> ALTER TABLE Vehicle ADD CHECK (Source <> 'apple sauce');

mysql> SELECT tc.constraint_schema, tc.constraint_name, tc.table_name, tc.constraint_type, tc.enforced, cc.check_clause
FROM information_schema.TABLE_CONSTRAINTS tc
   LEFT JOIN information_schema.CHECK_CONSTRAINTS cc
   ON tc.CONSTRAINT_SCHEMA=cc.CONSTRAINT_SCHEMA
   AND tc.CONSTRAINT_NAME=cc.CONSTRAINT_NAME
WHERE
    tc.TABLE_NAME='Vehicle'
    AND tc.CONSTRAINT_TYPE='CHECK'\G;

*************************** 1. row ***************************
CONSTRAINT_SCHEMA: vehicle_db_development
  CONSTRAINT_NAME: vehicle_chk_1
       TABLE_NAME: Vehicle
  CONSTRAINT_TYPE: CHECK
         ENFORCED: YES
     CHECK_CLAUSE: (`Source` <> _utf8mb4\'apple sauce\')
1 row in set (0.01 sec)

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

8 Answers8

Linked