get primary keys of underlaying tables in a view-CodePudding

I know of show tables and show columns from table TABLE_NAME which gets me the primary key (Key is PRI). But how do I get the primary key of a view or more specific the keys of the underlying tables? In that case show columns from VIEW_NAME all the Key values are empty.

CodePudding user response：

A new INFORMATION_SCHEMA table exists to map views to the underlying table(s). It's called VIEW_TABLE_USAGE.

mysql> create table mytable (id serial primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> create view v as select * from mytable;
Query OK, 0 rows affected (0.00 sec)
    
mysql> select table_schema, table_name, column_name, ordinal_position
 from information_schema.view_table_usage 
 join information_schema.key_column_usage using (table_schema, table_name) 
 where table_name='mytable' and constraint_name='PRIMARY';
 -------------- ------------ ------------- ------------------ 
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION |
 -------------- ------------ ------------- ------------------ 
| test2        | mytable    | id          |                1 |
 -------------- ------------ ------------- ------------------

This table is new in MySQL 8.0.13. If you use an older version of MySQL (or any version of MariaDB), there is no such information_schema table, so you can't get this information by any query. You would need to come up with a way to parse the view definition, which is a complex task because it could have a wide variety of SELECT syntax.

mysql> select view_definition from information_schema.views where table_name='v';
 -------------------------------------------------------------- 
| VIEW_DEFINITION                                              |
 -------------------------------------------------------------- 
| select `test2`.`mytable`.`id` AS `id` from `test2`.`mytable` |
 --------------------------------------------------------------