mpopp.net blog – Markus Popp

Let me give you another great example that demonstrates the power of information_schema.

Assume that we create two tables which are combined by a foreign key constraint:

mysql> CREATE TABLE tt1 (
    ->   id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->   val INT UNSIGNED NOT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE tt2 (
    ->   id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->   id_1 INT UNSIGNED NOT NULL,
    ->   val INT UNSIGNED NOT NULL,
    ->   FOREIGN KEY (id_1) REFERENCES tt1(id)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

What if you want to know if there are any tables that reference on table tt1 and which ones they are? Well, you could try to delete the table and you’ll get an error message telling you the foreign key constraints, if there are some. But if there aren’t any, your table will be lost – so definitely no good idea.

The solution is simple with MySQL 5 – information_schema provides all information that you need to pack this into a Stored Procedure (that I store in a database ‘funcs’):

mysql> DELIMITER //
mysql>
mysql> DROP PROCEDURE IF EXISTS getDeps //
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE getDeps(pDatabase VARCHAR(200), 
                                pTable VARCHAR(200))
    -> SQL SECURITY INVOKER
    -> BEGIN
    ->
    ->   SET @sql := CONCAT('SELECT
    '>           CONCAT(REFERENCED_COLUMN_NAME, '' -> ('',
    '>                  TABLE_SCHEMA, ''.'',
    '>                  TABLE_NAME, '') '',
    '>                  COLUMN_NAME) AS `references`
    '>           FROM information_schema.KEY_COLUMN_USAGE
    '>           WHERE REFERENCED_TABLE_SCHEMA = ''', pDatabase, '''
    '>             AND REFERENCED_TABLE_NAME = ''', pTable, '''
    '>           ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME');
    ->
    ->   PREPARE pSql FROM @sql;
    ->
    ->   EXECUTE pSql;
    ->
    -> END //
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> DELIMITER ;

So try it out:

mysql> call funcs.getDeps(database(), 'tt1');
+-----------------------+
| references            |
+-----------------------+
| id -> (test.tt2) id_1 |
+-----------------------+
1 row in set (0.61 sec)

Query OK, 0 rows affected (0.61 sec)

Let’s add another table that references to tt1:

mysql> CREATE TABLE tt3 LIKE tt2;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE tt3
    ->   ADD FOREIGN KEY (id_1) REFERENCES tt1(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> call funcs.getDeps(database(), 'tt1');
+-----------------------+
| references            |
+-----------------------+
| id -> (test.tt2) id_1 |
| id -> (test.tt3) id_1 |
+-----------------------+
2 rows in set (0.53 sec)

Query OK, 0 rows affected (0.53 sec)

Works great!

However, initially I wanted to automatically use the current database inside the Stored Procedure, but there was a problem: the Stored Procedure resides in the database ‘funcs’. If we call the procedure from database ‘test’, the procedure would return the database ‘funcs’ if the procedure asks for database() – not the database ‘test’. That’s not very intuitive, I think – is it a bug?

However – you can still do great things with information_schema. Just let your mind flow.