How can I define a composite primary key in any Mysql database table?
How can I define a composite primary key in any Mysql database table? or
UPDATE 2 which SQL query should be used to display the indexes of any table that contains composite primary keys?
I have many tables in mysql database that have composite keys of 2 or 3 primary keys, I am using phpmyadmin and I need to write php code to determine which table has composite keys, now I can determine the primary key of a table with request
SHOW INDEXES FROM '".$row3['TABLE_NAME']."' WHERE Key_name = 'PRIMARY'
which gives me what I want, but now how can I find out the indices where I have the composite keys?
UPDATE 1
In the context of Daniel Image's comment for viewing composite primary keys in PHPMyAdmin
composite primary keys look like this in phpmyadmin:
a source to share
UPDATE:
In addition to the updated question, you can use the following in your PHP script:
SELECT COUNT(*) num_keys
FROM information_schema.KEY_COLUMN_USAGE
WHERE table_name ='tb' AND constraint_name = 'PRIMARY';
This query will return num_keys
> 1 if the table tb
has a composite primary key.
I'm not sure if I understood what you are trying to achieve, but you might consider using it SHOW INDEX
like this:
CREATE TABLE tb (a int, b int, c int);
Query OK, 0 rows affected (0.21 sec)
ALTER TABLE tb ADD CONSTRAINT pk_tb PRIMARY KEY (a, b);
Query OK, 0 rows affected (0.06 sec)
SHOW INDEX FROM tb WHERE key_name='PRIMARY';
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tb | 0 | PRIMARY | 1 | a | A | NULL | NULL | NULL | | BTREE | |
| tb | 0 | PRIMARY | 2 | b | A | 0 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.02 sec)
If it weren't for a complex key, you would only get one line per request SHOW INDEX
:
CREATE TABLE tb2 (a int, b int, c int);
Query OK, 0 rows affected (0.05 sec)
ALTER TABLE tb2 ADD CONSTRAINT pk_tb PRIMARY KEY (a);
Query OK, 0 rows affected (0.05 sec)
SHOW INDEX FROM tb2 WHERE key_name='PRIMARY';
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| tb2 | 0 | PRIMARY | 1 | a | A | 0 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.02 sec)
a source to share