How to get primary key of table

Question

Is there a way to get the name of primary key field from mysql-database  For example   I have a table like this    ---- ------    id   name    ---- ------    1    Foo1     2    Foo2     3    Foo3    ---- ------    Where the field id is primary key  it has auto increment but I can t use that   How can I retrieve fields name  id  in php

User · Answer

I use SHOW INDEX FROM table   it gives me alot of informations   if the key is unique  its sequenece in the index  the collation  sub part  if null  its type and comment if exists  see screenshot here

User · Answer

You should use PRIMARY from key column usage constraint name    PRIMARY   sample query   SELECT k column name as PK  concat tbl TABLE SCHEMA        tbl TABLE NAME       as TABLE NAME FROM information schema TABLES tbl JOIN information schema key column usage k on k table name   tbl table name WHERE k constraint name  PRIMARY    AND tbl table schema  MYDB    AND tbl table type  BASE TABLE

User · Answer

If you have spatial tables in your database  use   SHOW KEYS FROM table WHERE Key name    PRIMARY  OR Key name    OGR FID

User · Answer

A better way is to use SHOW KEYS since you don t always have access to information schema  The following works   SHOW KEYS FROM table WHERE Key name    PRIMARY    Column name will contain the name of the primary key

User · Answer

Shortest possible code seems to be something like       dblink contain database login details      tblName the current table name   r   mysqli fetch assoc mysqli query  dblink   SHOW KEYS FROM  tblName WHERE Key name    PRIMARY        iColName    r  Column name

User · Answer

SELECT k column name FROM information schema key column usage k    WHERE k table name    YOUR TABLE NAME  AND k constraint name LIKE  pk     I would recommend you to watch all the fields

User · Answer

For a PHP approach  you can use mysql field flags   q   mysql query  select   from table limit 1     for  i   0   i  lt  mysql num fields     i        if strpos mysql field tags  q   i    primary key       false          echo mysql field name  q   i    is a primary key n

User · Answer

How about this  SELECT COLUMN NAME FROM INFORMATION SCHEMA COLUMNS WHERE TABLE SCHEMA    Your Database    AND TABLE NAME    Your Table name    AND COLUMN KEY    PRI     SELECT COLUMN NAME FROM INFORMATION SCHEMA COLUMNS WHERE TABLE SCHEMA    Your Database    AND TABLE NAME    Your Table name    AND COLUMN KEY    UNI

User · Answer

use   show columns from tablename where  Key     PRI

User · Answer

I ve got it  finally    lt  php  function mysql get prim key  table    sql    SHOW INDEX FROM  table WHERE Key name    PRIMARY     gp   mysql query  sql    cgp   mysql num rows  gp   if  cgp  gt  0      Note I m not using a while loop because I never use more than one prim key column  agp   mysql fetch array  gp   extract  agp   return  Column name    else  return false          gt

User · Answer

If you want to generate the list of primary keys dynamically via PHP in one go without having to run through each table you can use SELECT TABLE NAME  COLUMN NAME FROM INFORMATION SCHEMA key column usage  WHERE table schema     database name  AND CONSTRAINT NAME    PRIMARY    though you do need to have access to the information schema to do this

User · Answer

Here is the Primary key Column Name  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  YourDatabase    AND t table name  YourTable

User · Answer

MySQL has a SQL query  SHOW INDEX FROM  which returns the indexes from a table  For eg  - the following query will show all the indexes for the products table -  SHOW INDEXES FROM products  G   It returns a table with type  column name  Key name  etc  and displays output with all indexes and primary keys as -                               1  row                                     Table  products    Non unique  0      Key name  PRIMARY  Seq in index  1   Column name  product id     Collation  A   Cardinality  0      Sub part  NULL        Packed  NULL          Null      Index type  BTREE       Comment   Index comment     To just display primary key from the table use  -   SHOW INDEXES FROM table name WHERE Key name    PRIMARY

User · Answer

SELECT kcu column name  kcu ordinal position FROM   information schema table constraints tc INNER JOIN information schema key column usage kcu ON     tc CONSTRAINT CATALOG   kcu CONSTRAINT CATALOG AND    tc CONSTRAINT SCHEMA   kcu CONSTRAINT SCHEMA AND    tc CONSTRAINT NAME   kcu CONSTRAINT NAME WHERE  tc table schema   schema               -- only look in the current schema AND    tc constraint type    PRIMARY KEY  AND    tc table name     lt your-table-name gt      -- specify your table  ORDER BY kcu ordinal position

[php] How to get primary key of table?

Examples related to php

Examples related to mysql

Examples related to primary-key