// you’re reading...

MySQL

Identifying InnoDB Foreign Key Constraints

When using InnoDB tables in MySQL, the option exists to apply foreign key constraints. Data modelers do this for various reasons, which I’m not going to explore here. What’s important is that these constraints are religion for DBAs (and client software) and have to be respected to maintain data integrity. Knowing the hierarchy of data dependencies is part of any due diligence prior to performing schema or application changes. Here are some techniques to getting the information you want.

How do I find all the constraints in all my databases?

In most cases with MySQL, the INFORMATION_SCHEMA is your friend and will gladly answer questions. It’s worth some exploration here, so let’s see what we can find:

mysql> SHOW TABLES IN INFORMATION_SCHEMA LIKE '%CONSTRAINT%';
+---------------------------------------------+
| Tables_in_INFORMATION_SCHEMA (%CONSTRAINT%) |
+---------------------------------------------+
| REFERENTIAL_CONSTRAINTS                     |
| TABLE_CONSTRAINTS                           |
+---------------------------------------------+
2 rows in set (0.01 sec)

Looks like there’s two tables that deal directly with constraints. What’s under the hood?

mysql> DESC INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS;
+---------------------------+--------------+------+-----+---------+-------+
| Field                     | Type         | Null | Key | Default | Extra |
+---------------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG        | varchar(512) | YES  |     | NULL    |       |
| CONSTRAINT_SCHEMA         | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_NAME           | varchar(64)  | NO   |     |         |       |
| UNIQUE_CONSTRAINT_CATALOG | varchar(512) | YES  |     | NULL    |       |
| UNIQUE_CONSTRAINT_SCHEMA  | varchar(64)  | NO   |     |         |       |
| UNIQUE_CONSTRAINT_NAME    | varchar(64)  | YES  |     | NULL    |       |
| MATCH_OPTION              | varchar(64)  | NO   |     |         |       |
| UPDATE_RULE               | varchar(64)  | NO   |     |         |       |
| DELETE_RULE               | varchar(64)  | NO   |     |         |       |
| TABLE_NAME                | varchar(64)  | NO   |     |         |       |
| REFERENCED_TABLE_NAME     | varchar(64)  | NO   |     |         |       |
+---------------------------+--------------+------+-----+---------+-------+
11 rows in set (0.00 sec)
mysql> DESC INFORMATION_SCHEMA.TABLE_CONSTRAINTS;
+--------------------+--------------+------+-----+---------+-------+
| Field              | Type         | Null | Key | Default | Extra |
+--------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG | varchar(512) | YES  |     | NULL    |       |
| CONSTRAINT_SCHEMA  | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_NAME    | varchar(64)  | NO   |     |         |       |
| TABLE_SCHEMA       | varchar(64)  | NO   |     |         |       |
| TABLE_NAME         | varchar(64)  | NO   |     |         |       |
| CONSTRAINT_TYPE    | varchar(64)  | NO   |     |         |       |
+--------------------+--------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

What’s the difference between REFERENTIAL_CONSTRAINTS and TABLE_CONSTRAINTS?

It looks like REFERENTIAL_CONSTRAINTS contains information about what types of constraints reference (duh?!?) a specific table such as what operations will be triggered in the event of a change. Let’s say that the constraint ON UPDATE CASCADE is applied to a dependent table. If the FOREIGN KEY is changed on the parent table, that FK will also change (UPDATE) on the child table. Pretty cool. This type of constraint is intended to maintain data integrity, even if keys change. Here’s an example of a record in the REFERENTIAL_CONSTRAINTS table:

mysql> SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME = 'apps_ver_instances_ibfk_1'\G
*************************** 1. row ***************************
CONSTRAINT_CATALOG: NULL
CONSTRAINT_SCHEMA: production_db
CONSTRAINT_NAME: apps_ver_instances_ibfk_1
UNIQUE_CONSTRAINT_CATALOG: NULL
UNIQUE_CONSTRAINT_SCHEMA: production_db
UNIQUE_CONSTRAINT_NAME: PRIMARY
MATCH_OPTION: NONE
UPDATE_RULE: RESTRICT
DELETE_RULE: RESTRICT
TABLE_NAME: apps_ver_instances
REFERENCED_TABLE_NAME: apps_vers
1 row in set (1.51 sec)

We can see that the database is: production_db, the child table is: apps_ver_instances and the parent table is: apps_vers and the constraint is on the primary key of the apps_vers table. Let’s see what the table constraints for the apps table are:

mysql> SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = 'apps_ver_instances_ibfk_1'\G
*************************** 1. row ***************************
CONSTRAINT_CATALOG: NULL
CONSTRAINT_SCHEMA: production_db
CONSTRAINT_NAME: apps_ver_instances_ibfk_1
TABLE_SCHEMA: production_db
TABLE_NAME: apps_ver_instances
CONSTRAINT_TYPE: FOREIGN KEY
1 row in set (1.13 sec)

Now you can see the callback, in the CONSTRAINT_NAME and CONSTRAINT_TYPE field.

What next?

Prior to execution of a change, constraints have to be evaluated to ensure success. Make sure production constraints exist across all environments to ensure a successful rollout plan.

For more information:
http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

Discussion

No comments for “Identifying InnoDB Foreign Key Constraints”

Post a comment

Help support my site and buy a domain name at http://domainsemailhosting.com/

%d bloggers like this: