no_pk_innodb_tables
NAME
no_pk_innodb_tables: List InnoDB tables where no PRIMARY KEY is definedTYPE
ViewDESCRIPTION
InnoDB uses a clustered B+ tree as underlying data structure. Data is clustered via clustering index, which is the PRIMARY KEY in InnoDB. It follows that any InnoDB table has a PRIMARY KEY, whether one was explicitly defined or not.
When no PRIMARY KEY is defined, InnoDB chooses an existing UNIQUE KEY on the table (but does not let us know which). When no such key is available, it creates an internal PRIMARY KEY, based on row id. However, it does not provide access to this data. This leads to a table clustered by some value we cannot access, control, nor define. It is generally bad practice to create an InnoDB table with no explicit PRIMARY KEY.
no_pk_innodb_tables lists InnoDB tables where PRIMARY KEY is not explicitly created. It offers a list of candidate keys: UNIQUE keys already defined, which are eligible to take the part of PRIMARY KEY.
STRUCTURE
mysql> DESC common_schema.no_pk_innodb_tables; +----------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+-------------+------+-----+---------+-------+ | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | ENGINE | varchar(64) | YES | | NULL | | | candidate_keys | longtext | YES | | NULL | | +----------------+-------------+------+-----+---------+-------+
SYNOPSIS
Columns of this view:
- TABLE_SCHEMA: schema of InnoDB table missing PRIMARY KEY
- TABLE_NAME: InnoDB table missing PRIMARY KEY
- ENGINE: currently the constant 'InnoDB'
- candidate_keys: Comma seperated list of candidate (UNIQUE) keys, or NULL if no such keys are available.
EXAMPLES
Show foreign keys create/drop statements for `sakila`.`film_actor` (depends on `film` and `actor` tables)
mysql> ALTER TABLE `sakila`.`rental` MODIFY rental_id INT NOT NULL, DROP PRIMARY KEY, ADD UNIQUE KEY(rental_id); mysql> CREATE TABLE `test`.`no_pk` (id INT) ENGINE=InnoDB; mysql> SELECT * FROM common_schema.no_pk_innodb_tables; +--------------+------------+--------+-----------------------+ | TABLE_SCHEMA | TABLE_NAME | ENGINE | candidate_keys | +--------------+------------+--------+-----------------------+ | sakila | rental | InnoDB | rental_date,rental_id | | test | no_pk | InnoDB | NULL | +--------------+------------+--------+-----------------------+