I'm wondering if there is a way to programmatically list table relationships in a database, and their type, based on foreign key relationships?
Take for example these tables:
CREATE TABLE `a` (
`id` int NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `b` (
`id` int NOT NULL,
`a_id` int NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk.b.a.b` FOREIGN KEY (`a_id`) REFERENCES `a` (`id`)
) ENGINE=InnoDB;
CREATE TABLE `c` (
`id` int NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `b_c` (
`b_id` int NOT NULL,
`c_id` int NOT NULL,
PRIMARY KEY (`b_id`,`c_id`),
CONSTRAINT `fk.b_c.b.c` FOREIGN KEY (`c_id`) REFERENCES `c` (`id`),
CONSTRAINT `fk.b_c.c.b` FOREIGN KEY (`b_id`) REFERENCES `b` (`id`)
) ENGINE=InnoDB;
We can get the foreign key relationships with this query:
SELECT
table_name 'table',
column_name 'column',
referenced_table_name 'referenced_table',
referenced_column_name 'referenced_column'
FROM
information_schema.key_column_usage
WHERE
referenced_table_name IS NOT NULL
AND table_schema = 'test';
table column referenced_table referenced_column
b a_id a id
b_c c_id c id
b_c b_id b id
Now... I think the relationship information above should be enough to deduce what relationships exists and their types, but I'm not able to translate it into an algorithm... To answer my original question: I know there is a way, but haven't been able to find it.