Get the primary key of the table?

Question

Is there any way to get primary key field name from mysql database? For example:

I have a table like this:

+----+------+ | id | name | +----+------+ | 1 | Foo1 | | 2 | Foo2 | | 3 | Foo3 | +----+------+ 

If the field identifier is the primary key (it has auto-increment, but I cannot use it). How to get field name "id" in php?

Answer 1

It is best to use SHOW KEYS , as you do not always have access to schema information. The following works:

 SHOW KEYS FROM table WHERE Key_name = 'PRIMARY' 

The column name will contain the name of the primary key.

Answer 2

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'; 

Answer 3

 SELECT kcu.column_name FROM information_schema.key_column_usage WHERE table_schema = schema() -- only look in the current db AND constraint_name = 'PRIMARY' -- always 'PRIMARY' for PRIMARY KEY constraints AND table_name = '<your-table-name>' -- specify your table. 

Answer 4

How about this one.

 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'; 

Answer 5

using:

 show columns from tablename where `Key` = "PRI"; 

Answer 6

For the PHP approach you can use mysql_field_flags

 $q = mysql_query('select * from table limit 1'); for($i = 0; $i < 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"; 

Answer 7

If you want to dynamically generate a list of primary keys with php in one pass 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' 

although for this you need access to .schema information.

Answer 8

I did it finally!

 <?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 > 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); } } ?> 

Answer 9

The 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']; 

Answer 10

I am using SHOW INDEX FROM table; it gives me a lot of information; if the key is unique, its sequence in the index, sorting, sub-item, if null, its type and comment, if they exist, see screenshot here

Answer 11

MySQL has a SQL query "SHOW INDEX FROM" that returns indexes from a table. E.g. - the following query will show all indexes for the product table: -

 SHOW INDEXES FROM products \G 

It returns a table with type, column_name, table_name, etc. and displays the 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 simply display the primary key from the table, use: -

 SHOW INDEXES FROM table_name WHERE Key_name = 'PRIMARY'