Month: January 2023
Foreign Key Displays
For foreign keys, I like to see what references what, It’s easy to find but the challenge is to decide how to display.
Showing a result with the mysql client:
prompt> call show_fk('t2'); +-------------+-----------------+------------+ | referencing | constraint_name | referenced | +-------------+-----------------+------------+ | t2 | mx | t3 | | s1 | | s1 | | s2 | | s2 | | | | | | t2 | t2_ibfk_1 | t1 | | s1 | | s1 | +-------------+-----------------+------------+ 6 rows in set (0.08 sec) Query OK, 6 rows affected (0.08 sec)
That is, for table T2, I show the table name and foreign-key name and referenced-table name, then the columns of that table in the order they appear in the foreign key, then if there is another foreign key repeat after a blank line.
Here is a long but simple stored procedure that produces such a display.
DROP PROCEDURE show_fk; CREATE PROCEDURE show_fk(ref_table_name VARCHAR(128)) BEGIN DECLARE d_table_name VARCHAR(128); DECLARE d_column_name VARCHAR(128); DECLARE d_ordinal_position INT; DECLARE d_constraint_name VARCHAR(128); DECLARE d_referenced_table_name VARCHAR(128); DECLARE d_referenced_column_name VARCHAR(128); DECLARE counter INT DEFAULT 0; DECLARE err INT DEFAULT 0; DECLARE x CURSOR FOR SELECT table_name, column_name, ordinal_position, constraint_name, referenced_table_name, referenced_column_name FROM information_schema.key_column_usage WHERE table_name = ref_table_name AND referenced_column_name IS NOT NULL ORDER BY constraint_name, ordinal_position; DECLARE CONTINUE HANDLER FOR NOT FOUND SET err = 1; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION set err = 1; CREATE TEMPORARY TABLE fks (referencing VARCHAR(128), constraint_name VARCHAR(128), referenced VARCHAR(128)); OPEN x; WHILE err = 0 DO FETCH x INTO d_table_name,d_column_name, d_ordinal_position, d_constraint_name, d_referenced_table_name, d_referenced_column_name; IF err = 0 THEN IF counter <> 0 AND d_ordinal_position = 1 THEN INSERT INTO fks VALUES ('', '', ''); END IF; IF d_ordinal_position = 1 THEN INSERT INTO fks VALUES (d_table_name, d_constraint_name, d_referenced_table_name); END IF; INSERT INTO fks VALUES (d_column_name, '', d_referenced_column_name); SET counter = counter + 1; END IF; END WHILE; CLOSE x; SELECT * FROM fks; DROP TABLE fks; END; CALL show_fk('t2');
This is vaguely like an entity-relationship diagram, but with tables rather than pictures.
The flaws are: (1) it needs extra privileges, (2) it mixes different object types. So let’s look at the simpler and more common type of display.
Everything necessary can come from information_schema.key_column_usage.
For example, there’s a GUI that displays with these columns:
Name | Schema | Table | Column |Referenced Schema | Referenced Table | Referenced Column
That’s easy to reproduce with
SELECT constraint_name AS `Name`, table_schema AS `Schema`, table_name AS `Table`, column_name AS `Column`, referenced_table_schema AS `Referenced Schema`, referenced_table_name AS `Referenced Table`, referenced_column_name AS `Referenced Column` FROM information_schema.key_column_usage WHERE referenced_column_name IS NOT NULL ORDER BY constraint_name, ordinal_position;
Or if that takes too long to type, make a view.
There’s another GUI that displays with these columns:
Key name | Columns | Reference Table | Foreign Columns | On UPDATE | On DELETE
(The “On UPDATE” and “On DELETE” values would have to come from information_schema.referential_constraints.)
The objection that I’d make is that such headers are not standard. So anybody who knows the actual column names has to do a double take, wondering whether the first column is the same as “constraint_name” or something exotic, and so on. Use of multiple different names for the same thing is poetry not programming.
So I think this display, which admittedly makes cosmetic changes (replacing ‘_’ with ‘ ‘ and changing upper case to mixed case and emphasizing one column) is better:
I’ve made it so that can come from user statements or from the explorer. The source code is downloadable now and the released executables will come soon.