In MySQL we can use the information_schema.key_column_usage table to get information about the keys in a table. This table returns one row for each column that is constrained as a key.
We can use this table to find out which column/s is the primary key of a given table and to return any foreign keys from the table.
Example
Here’s an example of querying the information_schema.key_column_usage table to find out which columns are constrained as a key in the specified table:
SELECT *
FROM information_schema.key_column_usage
WHERE table_schema = 'krankykranes'
AND table_name = 'Orders';Result:
+--------------------+-------------------+---------------------+---------------+--------------+------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | POSITION_IN_UNIQUE_CONSTRAINT | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME | +--------------------+-------------------+---------------------+---------------+--------------+------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+ | def | krankykranes | PRIMARY | def | krankykranes | Orders | OrderId | 1 | NULL | NULL | NULL | NULL | | def | krankykranes | FK_Orders_Customers | def | krankykranes | Orders | CustomerId | 1 | 1 | krankykranes | Customers | CustomerId | +--------------------+-------------------+---------------------+---------------+--------------+------------+-------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+
I ran this example against the krankykranes database in MySQL, and I checked the Orders table. We can see by the results that the OrderId is the primary key.
We can also see that there’s a foreign key called FK_Orders_Customers. While there’s nothing that explicitly categorises these as the primary key or foreign key, their names and other information in the rows provide a good clue. In MySQL all primary keys are called PRIMARY.
As for the foreign key, it starts with FK_ (which is often the case, unless it was explicitly assigned another name). We can also see the table and columns that the foreign key references.
Composite Primary Key Example
Here’s an example that involves a composite primary key:
SELECT
column_name,
constraint_name,
ordinal_position
FROM information_schema.key_column_usage
WHERE table_schema = 'krankykranes'
AND table_name = 'orderitems';Result:
+-------------+------------------------+------------------+ | COLUMN_NAME | CONSTRAINT_NAME | ORDINAL_POSITION | +-------------+------------------------+------------------+ | OrderId | PRIMARY | 1 | | OrderItemId | PRIMARY | 2 | | OrderId | FK_OrderItems_Orders | 1 | | ProductId | FK_OrderItems_Products | 1 | +-------------+------------------------+------------------+
We can see by the results that the OrderId and OrderItemId columns comprise its primary key. We know it’s a composite primary key, because more than one column is listed as PRIMARY (which means that the primary key comprises of two columns, making it a composite key).
Note that the ORDINAL_POSITION column specifies the column’s position within the constraint (not the column’s position within the table).
We can also see that there are two foreign key constraints on this table.
Check the Whole Database
We can remove the table_schema column from our filtering criteria in order to list all tables from the database along with their key constraints.
SELECT
table_name,
column_name,
constraint_name,
ordinal_position
FROM information_schema.key_column_usage
WHERE table_schema = 'krankykranes';Result:
+------------+-------------+------------------------+------------------+ | TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | ORDINAL_POSITION | +------------+-------------+------------------------+------------------+ | Cats | CatId | PRIMARY | 1 | | Customers | CustomerId | PRIMARY | 1 | | Customers2 | CustomerId | PRIMARY | 1 | | Dogs | DogId | PRIMARY | 1 | | Employees | Id | PRIMARY | 1 | | Idiots | IdiotId | PRIMARY | 1 | | OrderItems | OrderId | PRIMARY | 1 | | OrderItems | OrderItemId | PRIMARY | 2 | | OrderItems | OrderId | FK_OrderItems_Orders | 1 | | OrderItems | ProductId | FK_OrderItems_Products | 1 | | Orders | OrderId | PRIMARY | 1 | | Orders | CustomerId | FK_Orders_Customers | 1 | | Products | ProductId | PRIMARY | 1 | | Products | VendorId | FK_Products_Vendors | 1 | | Vendors | VendorId | PRIMARY | 1 | +------------+-------------+------------------------+------------------+
Here I included the table_name column in the output so that we could see which table each constraint belongs to.
Check the Whole Server
And going a step further, we can remove the filtering criteria altogether in order to return information about all tables on the server:
SELECT * FROM information_schema.key_column_usage;I won’t post the results here, as the result set is quite large. In this example I also decided to use the asterisk wildcard (*) to return all rows, but go ahead and choose whichever rows suit your needs.