how to get the list of all constraints in MySQL database

In MySQL, you can retrieve information about constraints (rules) in a database by querying the information schema tables. The information schema contains metadata about the database, including details about tables, columns, and constraints. To get a list of constraints in MySQL, you can use the following queries.

Syntax :

SELECT
    constraint_name,
    constraint_type,
    table_name
FROM
    information_schema.table_constraints
WHERE
    table_schema = 'your_database_name';

Replace 'your_database_name' with the actual database name.

Query :

select constraint_name, constraint_type, table_name from information_schema.table_constraints where table_schema = 'learn_sql';

Ouput :

+---------------------+-----------------+------------------+
| CONSTRAINT_NAME     | CONSTRAINT_TYPE | TABLE_NAME       |
+---------------------+-----------------+------------------+
| fk_users            | FOREIGN KEY     | addresses        |
| email               | UNIQUE          | employees        |
| PRIMARY             | PRIMARY KEY     | employees        |
| employees_chk_1     | CHECK           | employees        |
| employees_chk_2     | CHECK           | employees        |
| PRIMARY             | PRIMARY KEY     | orders           |
| fk_orders_employees | FOREIGN KEY     | orders           |
| fk_users_orders     | FOREIGN KEY     | orders           |
| PRIMARY             | PRIMARY KEY     | sales            |
| PRIMARY             | PRIMARY KEY     | statement_backup |
| email               | UNIQUE          | users            |
| PRIMARY             | PRIMARY KEY     | users            |
+---------------------+-----------------+------------------+
12 rows in set (0.00 sec)

Above query will return the constraint name, constraint type, table name from information_schema database for the learn_sql database.