CODEWITHSUNDEEP
mariadb
codelifevcd
codelifevcd

Reputation: 317

How to see constraints values in mariadb (return empty result)?

I need to unactive or active some constraints in MariaDB. So I did:

SET FOREIGN_KEY_CHECKS=0
SET FOREIGN_KEY_CHECKS=1

or

SET SESSION foreign_key_checks=OFF;
SET SESSION foreign_key_checks=ON;

Next I need to check the constraints values to be sure of what I did. I tried:

select constraint_name,
       constraint_schema as table_schema,
       table_name,
       check_clause as definition
from information_schema.check_constraints
order by constraint_name;

or

SELECT * from INFORMATION_SCHEMA.CHECK_CONSTRAINTS;

It returns empty results and I don't know why.

How can I see the constraints values for mariadb 10.3.16 ?

Upvotes: 0

Views: 88

Answers (1)

Joel
Joel

Reputation: 1239

Why not check the value of the global variable ?

MariaDB [(none)]> SET SESSION foreign_key_checks=OFF;
Query OK, 0 rows affected (0.003 sec)

MariaDB [(none)]> SELECT @@foreign_key_checks;
+----------------------+
| @@foreign_key_checks |
+----------------------+
|                    0 |
+----------------------+
1 row in set (0.002 sec)

MariaDB [(none)]> SET SESSION foreign_key_checks=ON;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SELECT @@foreign_key_checks;
+----------------------+
| @@foreign_key_checks |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.000 sec)

Works too with SHOW VARIABLES

MariaDB [(none)]> SHOW VARIABLES LIKE 'foreign_key_checks';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | ON    |
+--------------------+-------+
1 row in set (0.008 sec)

Upvotes: 1

Related Questions