109

How do I get a list of all constraints from MySQL database?

user4157124
  • 2,809
  • 13
  • 27
  • 42
lolajl
  • 1,241
  • 2
  • 9
  • 7
  • 2
    There is more than one type of constraint in MySQL. Which do you mean? Can you give an example of the sort of thing you are looking for? – Mark Byers Jun 11 '10 at 17:50

5 Answers5

169

Use the information_schema.table_constraints table to get the names of the constraints defined on each table:

select *
from information_schema.table_constraints
where constraint_schema = 'YOUR_DB'

Use the information_schema.key_column_usage table to get the fields in each one of those constraints:

select *
from information_schema.key_column_usage
where constraint_schema = 'YOUR_DB'

If instead you are talking about foreign key constraints, use information_schema.referential_constraints:

select *
from information_schema.referential_constraints
where constraint_schema = 'YOUR_DB'
Senseful
  • 86,719
  • 67
  • 308
  • 465
  • 1
    This list will only include UNIQUE, PRIMARY KEY, or FOREIGN KEY constraints. CHECK is possible, but isn't enforced. DEFAULT constraints will not be visible using this query. – OMG Ponies Jun 11 '10 at 17:54
  • MySQL does not store CHECK constraints. If you try to define one, it parses and silently discards it. – Bill Karwin Jun 11 '10 at 18:18
  • 1
    The DEFAULT value doesn't count as a constraint. It's stored in `information_schema.columns.column_default`. – Bill Karwin Jun 11 '10 at 18:19
25

Great answer by @Senseful.

I am presenting modified query for those who are only looking for list of constraint names (and not other details/columns):

SELECT DISTINCT(constraint_name) 
FROM information_schema.table_constraints 
WHERE constraint_schema = 'YOUR_DB' 
ORDER BY constraint_name ASC;
Harshith J.V.
  • 877
  • 1
  • 8
  • 21
  • 2
    if you need to delete one once you find it, see here http://stackoverflow.com/a/838412/2401804 – r3wt Feb 02 '16 at 22:19
16

This really helps if you want to see primary and foreign key constraints as well as rules around those constraints such as ON_UPDATE and ON_DELETE and the column and foreign column names all together:

SELECT tc.constraint_schema,tc.constraint_name,tc.table_name,tc.constraint_type,kcu.table_name,kcu.column_name,kcu.referenced_table_name,kcu.referenced_column_name,rc.update_rule,rc.delete_rule

FROM information_schema.table_constraints tc

inner JOIN information_schema.key_column_usage kcu
ON tc.constraint_catalog = kcu.constraint_catalog
AND tc.constraint_schema = kcu.constraint_schema
AND tc.constraint_name = kcu.constraint_name
AND tc.table_name = kcu.table_name

LEFT JOIN information_schema.referential_constraints rc
ON tc.constraint_catalog = rc.constraint_catalog
AND tc.constraint_schema = rc.constraint_schema
AND tc.constraint_name = rc.constraint_name
AND tc.table_name = rc.table_name

WHERE tc.constraint_schema = 'my_db_name'

You may even want to add in some further information about those columns, simply add this into the SQL (and select the columns you want):

LEFT JOIN information_schema.COLUMNS c
ON kcu.constraint_schema = c.table_schema
AND kcu.table_name = c.table_name
AND kcu.column_name = c.column_name
friek108
  • 1,064
  • 1
  • 12
  • 23
0

To see the related constraints for your table just use:

select * from information_schema.table_constraints where CONSTRAINT_NAME like 'YOUR_TABLE%';
Deepanshu Mehta
  • 1,119
  • 12
  • 9
-3

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";

EmEsEn
  • 23
  • 4