Foreign Key Displays
source link: http://ocelot.ca/blog/blog/2023/01/01/foreign-key-displays/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
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.
The cute
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.
The usual
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.
The new
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.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK