A better way to get Primary Key columns

Permalink: http://bit.ly/1o0NdpY



When an application asks MySQL for the Primary Key of a table, there are several ways to go about doing this. A fast way would be to use these statements:

DESCRIBE `dbName`.`tableName`;
-- or
SHOW INDEX FROM `dbName`.`tableName` 
WHERE `Key_name` = 'PRIMARY';

The result set would have to be parsed in order to get the column names. This is not a recommended way to get the PK columns due to its limited usefulness as the column names cannot be returned INTO a variable.

Another method often used is this SELECT statement that uses a table JOIN:

SELECT k.`COLUMN_NAME`
FROM `information_schema`.`TABLE_CONSTRAINTS` t
JOIN `information_schema`.`KEY_COLUMN_USAGE` k
USING (`CONSTRAINT_NAME`, `TABLE_SCHEMA`, `TABLE_NAME`)
WHERE t.`CONSTRAINT_TYPE` = 'PRIMARY KEY'
 AND t.`TABLE_SCHEMA` = 'dbName'
 AND t.`TABLE_NAME` = 'tableName';

Depending on the number of tables and databases, this method may be slightly slower than the first method shown above. However, this method is more flexible as it allows a return of the output INTO a variable.

A better way to get the Primary Key columns is this SELECT statement that does not use a table JOIN:

SELECT `COLUMN_NAME`
FROM `information_schema`.`COLUMNS`
WHERE (`TABLE_SCHEMA` = 'dbName')
  AND (`TABLE_NAME` = 'tableName')
  AND (`COLUMN_KEY` = 'PRI');

Limitations: This is faster than the second method that uses a table JOIN. However, only the first method using DESCRIBE or SHOW INDEX can retrieve the PK columns of a TEMPORARY TABLE. This is because TEMPORARY TABLEs are invisible from the `information_schema` database (as of MySQL version 5.6).

Using the above formula, a function that retrieves the PK columns by passing the database and table names as parameters can be done. See the snippet below:

DELIMITER $$
DROP FUNCTION IF EXISTS `getPKColumns` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `getPKColumns`(
  dbName VARCHAR(64),
  tableName VARCHAR(64)) RETURNS text CHARSET utf8
BEGIN
  DECLARE PKColumns TEXT;
  SELECT GROUP_CONCAT(`COLUMN_NAME` SEPARATOR '`, `')
  FROM `information_schema`.`COLUMNS`
  WHERE (`TABLE_SCHEMA` = dbName)
    AND (`TABLE_NAME` = tableName)
    AND (`COLUMN_KEY` = 'PRI')
    INTO PKColumns;
  SET PKColumns = CONCAT('`', PKColumns, '`');
  RETURN PKColumns;
END $$
DELIMITER ;

Here's a sample that uses a SELECT statement to pass the database and table names in order to use the function:

SELECT getPKColumns('db', 'table');

The Primary Key column names returned are wrapped in backticks in case the name of the column turns out to be a reserved word. The function will correctly wrap the backticks even for cases where the table has multi-column Primary Keys. This implementation allows the function's return to be usable in many different scenarios such as in dynamic SQL statements. The output will be like:

`primaryKeyColumn1`, `primaryKeyColumn2`

Since Primary Keys are typically set up to be a single column or for lookup tables usually no more than two columns, the GROUP_CONCAT function can safely rely on the default value of group_concat_max_len which is at 1024 characters. There is no need to increase group_concat_max_len since a column name cannot exceed more than 64 characters which means there is very little risk of the result being truncated should the character count of the output exceed 1024.