MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘SQL/PSM’ tag

Capture MySQL Foreign Keys

with one comment

Shantanu asked a follow-up question on my Cleanup a MySQL Schema post from last month. He wanted to know if there was a way to capture foreign keys before removing them. The answer is yes, but how you do it depends on whether the primary key is based on a surrogate key using an auto incrementing sequence of a natural key using descriptive columns.

You can capture foreign keys with a simple query when they’re determined by a single column value. However, this script creates ALTER statements that will fail when a table holds a multiple column foreign key value. The SELECT statement would look like this when capturing all foreign key values in a MySQL Server:

1
2
3
4
5
6
7
8
9
10
11

SELECT   CONCAT('ALTER TABLE',' ',tc.table_schema,'.',tc.table_name,' '
               ,'ADD CONSTRAINT',' fk_',tc.constraint_name,' '
               ,'FOREIGN KEY (',kcu.column_name,')',' '
               ,'REFERENCES',' ',kcu.referenced_table_schema,'.',kcu.referenced_table_name,' ' 
               ,'(',kcu.referenced_column_name,');') AS script
FROM     information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu
ON       tc.constraint_name = kcu.constraint_name
AND      tc.constraint_schema = kcu.constraint_schema
WHERE    tc.constraint_type = 'foreign key'
ORDER BY tc.TABLE_NAME
,        kcu.column_name;

In MySQL 8 forward, you must make a case sensitive comparison, like:

9

WHERE    tc.constraint_type = 'FOREIGN KEY'

You would add a line in the WHERE clause to restrict it to a schema and a second line to restrict it to a table within a schema, like this:

AND      tc.table_schema = 'your_mysql_database'
AND      tc.table_name = 'your_table_name'

Unfortunately, when the primary and foreign keys involve two or more columns you require a procedure and function. The function because you need to read two cursors, and the NOT FOUND can’t be nested in the current deployment of MySQL’s SQL/PSM stored programs. In this example the storedForeignKeys procedure finds the table’s foreign key constraints, and the columnList function adds the column detail. The command_list table stores the commands to restore foreign key constraints.

The command_list table that stores the values is:

CREATE TABLE command_list
( command_list_id  INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
, sql_command      VARCHAR(6)    NOT NULL
, sql_object       VARCHAR(10)   NOT NULL
, sql_constraint   VARCHAR(11)
, sql_statement    VARCHAR(768)  NOT NULL);

This is the storedForeignKeys procedure:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75

CREATE PROCEDURE storeForeignKeys
( pv_schema_name  VARCHAR(64)
, pv_table_name   VARCHAR(64))
BEGIN
 
  /* Declare local variables. */
  DECLARE lv_schema_name              VARCHAR(64);
  DECLARE lv_table_name               VARCHAR(64);
  DECLARE lv_constraint_name          VARCHAR(64);
  DECLARE sql_stmt                    VARCHAR(1024);
 
  /* Declare control variable for handler. */
  DECLARE fetched    INT DEFAULT 0;
 
  /* Declare local cursor for foreign key table, it uses null replacement
     because the procedure supports null parameters. When you use null 
     parameters, you get all foreign key values. */
  DECLARE foreign_key_table CURSOR FOR
    SELECT   tc.table_schema
    ,        tc.table_name
    ,        tc.constraint_name
    FROM     information_schema.table_constraints tc
    WHERE    tc.table_schema = IFNULL(lv_schema_name, tc.table_schema)
    AND      tc.table_name = IFNULL(lv_table_name, tc.table_name)
    AND      tc.constraint_type = 'FOREIGN KEY'
    ORDER BY tc.table_name;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* Assign parameter values to local variables. */
  SET lv_schema_name := pv_schema_name;
  SET lv_table_name := pv_table_name;
 
  /* Open a local cursor. */  
  OPEN foreign_key_table;
  cursor_foreign_key_table: LOOP
 
    /* Fetch a row into the local variables. */
    FETCH foreign_key_table
    INTO  lv_schema_name
    ,     lv_table_name
    ,     lv_constraint_name;
 
    /* Catch handler for no more rows found from the fetch operation. */
    IF fetched = 1 THEN LEAVE cursor_foreign_key_table; END IF;
 
    /* The nested calls to the columnList function returns the list of columns
       in the foreign key. Surrogate primary to foreign keys can be resolved 
       with a simply query but natural primary to foreign key relationships
       require the list of columns involved in the primary and foreign key.
       The columnList function returns the list of foreign key columns in 
       the dependent table and the list of referenced columns (or the primary
       key columns) in the independent table. */
    SET sql_stmt := CONCAT('ALTER TABLE ',' ',lv_schema_name,'.',lv_table_name,' '
                          ,'ADD CONSTRAINT ',lv_constraint_name,' '
                          ,'FOREIGN KEY (',columnList(lv_schema_name,lv_table_name,lv_constraint_name));
 
    /* Record the SQL statements. */
    INSERT INTO command_list
    ( sql_command
    , sql_object
    , sql_constraint
    , sql_statement )
    VALUES
    ('ALTER'
    ,'TABLE'
    ,'FOREIGN KEY'
    , sql_stmt );
 
  END LOOP cursor_foreign_key_table;
  CLOSE foreign_key_table;  
 
END;
$$

This is the columnList function:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83

CREATE FUNCTION columnList
( pv_schema_name      VARCHAR(64)
, pv_table_name       VARCHAR(64)
, pv_constraint_name  VARCHAR(64)) RETURNS VARCHAR(512)
BEGIN
 
  /* Declare local variables. */
  DECLARE lv_schema_name              VARCHAR(64);
  DECLARE lv_table_name               VARCHAR(64);
  DECLARE lv_constraint_name          VARCHAR(64);
  DECLARE lv_column_count             INT UNSIGNED;
  DECLARE lv_column_name              VARCHAR(64);
  DECLARE lv_column_list              VARCHAR(512);
  DECLARE lv_column_ref_list          VARCHAR(64);
  DECLARE lv_referenced_table_schema  VARCHAR(64);
  DECLARE lv_referenced_table_name    VARCHAR(64);
  DECLARE lv_referenced_column_name   VARCHAR(64);
  DECLARE lv_return_string            VARCHAR(768);
 
  /* Declare control variable for handler. */
  DECLARE fetched    INT DEFAULT 0;
 
  /* Declare local cursor for foreign key column. */
  DECLARE foreign_key_column CURSOR FOR
    SELECT   kcu.column_name
    ,        kcu.referenced_table_schema
    ,        kcu.referenced_table_name
    ,        kcu.referenced_column_name
    FROM     information_schema.key_column_usage kcu
    WHERE    kcu.referenced_table_schema = lv_schema_name
    AND      kcu.table_name = lv_table_name
    AND      kcu.constraint_name = lv_constraint_name
    ORDER BY kcu.column_name;
 
  /* Declare a not found record handler to close a cursor loop. */
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = 1;
 
  /* Assign parameter values to local variables. */
  SET lv_schema_name := pv_schema_name;
  SET lv_table_name := pv_table_name;
  SET lv_constraint_name := pv_constraint_name;
 
  /* Set the first column value. */
  SET lv_column_count := 1;
 
  /* Open the nested cursor. */
  OPEN  foreign_key_column;
  cursor_foreign_key_column: LOOP
 
    /* Fetch a row into the local variables. */    
    FETCH foreign_key_column
    INTO  lv_column_name
    ,     lv_referenced_table_schema
    ,     lv_referenced_table_name
    ,     lv_referenced_column_name;
 
    /* Catch handler for no more rows found from the fetch operation. */
    IF fetched = 1 THEN LEAVE cursor_foreign_key_column; END IF;
 
    /* Initialize the column list or add to it. */
    IF lv_column_count = 1 THEN
      SET lv_column_list := lv_column_name;
      SET lv_column_ref_list := lv_referenced_column_name;
 
      /* Increment the counter value. */
      SET lv_column_count := lv_column_count + 1;
    ELSE
      SET lv_column_list := CONCAT(lv_column_list,',',lv_column_name);
      SET lv_column_ref_list := CONCAT(lv_column_ref_list,',',lv_referenced_column_name);
    END IF;
 
  END LOOP cursor_foreign_key_column;
  CLOSE foreign_key_column;
 
  /* Set the return string to a list of columns. */
  SET lv_return_string :=
        CONCAT(lv_column_list,')',' '
              ,'REFERENCES',' ',lv_referenced_table_schema,'.',lv_referenced_table_name,' ' 
              ,'(',lv_column_ref_list,');');
 
  RETURN lv_return_string;
END;
$$

You can call the procedure with a schema and table name, and you’ll get the foreign keys from just that table. You can create the following parent and child tables to test how multiple column foreign keys work in the script (provided because most folks use surrogate keys):

CREATE TABLE parent
( first_name  VARCHAR(20)  NOT NULL DEFAULT ''
, last_name   VARCHAR(20)  NOT NULL DEFAULT ''
, PRIMARY KEY (first_name, last_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
CREATE TABLE child
( child_name  VARCHAR(20)  NOT NULL
, first_name  VARCHAR(20)  DEFAULT NULL
, last_name   VARCHAR(20)  DEFAULT NULL
, PRIMARY KEY (child_name)
, KEY fk_parent(first_name, last_name)
, CONSTRAINT fk_parent FOREIGN KEY (first_name, last_name)
  REFERENCES parent (first_name, last_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

You call the storeForeignKeys procedure for the child table with this syntax:

CALL storeForeignKeys('studentdb', 'child');

You call the storeForeignKeys procedure for all tables in a schema with this syntax:

CALL storeForeignKeys('studentdb', null);

While unlikely you’ll need this, the following calls the storeForeignKeys procedure for all tables in all schemas:

CALL storeForeignKeys(null, null);

You can export the command sequence with the following command to a script file:

SELECT sql_statement
INTO OUTFILE 'c:/Data/MySQL/apply_foreign_keys.sql'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
FROM command_list;

While preservation of tables and foreign keys is best managed by using a tool, like MySQL Workbench, it’s always handy to have scripts to do specific tasks. I hope this helps those looking for how to preserve foreign keys. You also can find a comprehensive treatment on how to write SQL/PSM code in Chapter 14 of my Oracle Database 11g and MySQL 5.6 Developer Handbook.

Written by maclochlainn

March 17th, 2014 at 11:27 pm

Posted in Database Design,MySQL,SQL/PSM,Stored Procedures

Tagged with , ,