There are arbitrary tables and you need to know the name of the field with PRIMARY KEY , how can this be done by SQL (using MySQL ) with a query or using Python v3 tools and PyMySQL libraries?

There is a valid label of such a structure:

 CREATE TABLE `test` ( `name` text, `this_id` int(11) NOT NULL, PRIMARY KEY (`this_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And you need to return this_id

  • What is the expected result if the primary index is composite? - Akina
  • @Akina updated the question, in general faced with the situation when not all tables have PRIMARY KEY and even when it is, it is not always id. - users
  • There is a table: CREATE TABLE test (id bigint(20) NOT NULL, val int(11) NOT NULL, txt varchar(32) NOT NULL, PRIMARY KEY (id,txt(3),val)) . What needs to be returned in this case? - Akina
  • @Akina is generally an array: ['id', 'txt', 'val']. But there is no such type of tables in the database. And the question decided, with a slightly modified solution from L.Vadim, here’s a query ideone.com/mWcu21 - users
  • answer helped ???? - L. Vadim

2 answers 2

 SELECT pk.table_name, column_name as 'primary_key' FROM information_schema.table_constraints pk INNER JOIN information_schema.key_column_usage C on c.table_name = pk.table_name and c.constraint_name = pk.constraint_name where constraint_type = 'primary key' -- and pk.table_name LIKE '%whatever%'
  • Yes, the code works, you really have the error "C" to be in lower case, and for some tables returns more than 1 value. Here, for example, for such a plate ideone.com/IEMKm2 PS And yes, I added a choice of base to the rule - users
  • need to think how to do it - L. Vadim
  • In general, I corrected your request, and in general I get the desired result: ideone.com/mWcu21, differs in that JOIN is still across the field TABLE_SCHEMA - users
  • great so you can do - L. Vadim

Slightly changed the request from @L. Vadim :

 -- Выводим значения поля COLUMN_NAME из таблице information_schema.TABLE_CONSTRAINTS SELECT C.COLUMN_NAME FROM information_schema.TABLE_CONSTRAINTS AS pk /* Делаем INNER JOIN из-за того что нам нужны только пересечения полей: TABLE_NAME название таблицы, CONSTRAINT_NAME название поля с ключом TABLE_SCHEMA название базы */ INNER JOIN information_schema.KEY_COLUMN_USAGE AS C ON C.TABLE_NAME = pk.TABLE_NAME AND C.CONSTRAINT_NAME = pk.CONSTRAINT_NAME AND C.TABLE_SCHEMA = pk.TABLE_SCHEMA /* Условия: TABLE_NAME название таблицы, TABLE_SCHEMA название базы CONSTRAINT_TYPE поле где указан PRIMARY KEY */ WHERE pk.TABLE_NAME = 'test' AND pk.TABLE_SCHEMA = 'test_db' AND pk.CONSTRAINT_TYPE = 'PRIMARY KEY';

Option without comment:

 SELECT C.COLUMN_NAME FROM information_schema.table_constraints AS pk INNER JOIN information_schema.KEY_COLUMN_USAGE AS C ON C.TABLE_NAME = pk.TABLE_NAME AND C.CONSTRAINT_NAME = pk.CONSTRAINT_NAME AND C.TABLE_SCHEMA = pk.TABLE_SCHEMA WHERE pk.TABLE_NAME = 'test' AND pk.TABLE_SCHEMA = 'test_db' AND pk.CONSTRAINT_TYPE = 'PRIMARY KEY';

I am not sure of the text below, since it was written from a quick study of the tables: information_schema.TABLE_CONSTRAINTS and information_schema.KEY_COLUMN_USAGE

As I understand it, in the table information_schema.TABLE_CONSTRAINTS store information about all keys for all databases and connect them to the table information_schema.KEY_COLUMN_USAGE the field CONSTRAINT_NAME which stores the name of our key in this case and in PRIMARY information_schema.KEY_COLUMN_USAGE are all fields with the keys, field name in the field COLUMN_NAME