There is a mysql database and a PDO driver. I need to pull out the entire structure, including the foreign keys. How can I pull out all foreign keys without accessing information_schema ?

For example, I have information about the index:

 object(stdClass)#14 (13) { ["Table"]=> string(4) "table_name" ["Non_unique"]=> string(1) "1" ["Key_name"]=> string(9) "action_id" ["Seq_in_index"]=> string(1) "1" ["Column_name"]=> string(9) "action_id" ["Collation"]=> string(1) "A" ["Cardinality"]=> string(1) "8" ["Sub_part"]=> NULL ["Packed"]=> NULL ["Null"]=> string(0) "" ["Index_type"]=> string(5) "BTREE" ["Comment"]=> string(0) "" ["Index_comment"]=> string(0) "" }

How to find out which table and which column it refers to?

    2 answers 2

    Without INFORMATION_SCHEMA - no way. Only parse the output of SHOW CREATE TABLE , which, however, is not such a difficult task.

     CREATE TABLE parent ( id INT NOT NULL, PRIMARY KEY (id) ) ENGINE=INNODB; CREATE TABLE child ( id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ) ENGINE=INNODB;

    mysql> show create table child\G

     *************************** 1. row *************************** Table: child Create Table: CREATE TABLE `child` ( `id` int(11) DEFAULT NULL, `parent_id` int(11) DEFAULT NULL, KEY `par_ind` (`parent_id`), #вытаскиваем REFERENCES `parent` (`id`) регуляркой, или, если есть, SQL-парсером CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

      A foreign key is almost never realized in the form of a hard link, this link is always speculative. Yes, there is a FOREIGN KEY foreign key FOREIGN KEY - however, this is primarily a constraint, not a relationship.

      The action_id field can refer to the actions table or the versions table or any other table as you wish. You bind the tables either in multiple SELECT queries, or inside your application.

      That is why ERP diagrams are drawn for the database in order to reflect these links, which are not sewn into the scheme. Yes, there are keys, but connections appear only in SELECT queries, not in the schema.

      • What question do you answer? - strangeqargo 2:21 pm
      • You cannot generally find out which table the foreign key refers to. The most recent question at the end of the message. - cheops