[SOLVED] Finding the primary key in a table

[andy_b42]

I've been looking how to find the Primary Key of a table similar to the mysql equivalent of something like

 

show keys from table1 where Key_name = 'primary'

 

So far i have got this stage:

 

SELECT
kcu.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc on kcu.TABLE_NAME = tc.TABLE_NAME
WHERE
tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND kcu.TABLE_NAME = 'table1'

 

However, if the table contains a Foreign Key field that is a Primary Key on a nother table, it returns it in the query.

 

Does any one know of a way to return ONLY the Primary Key of a table and nothing else?

 

Thanks

[andy_b42]

Solved, i was joining my tables on the table name and not the constraint name (which is where i should have been joining).

 

Incase this is of use to anyone:

 

SELECT  
    kcu.COLUMN_NAME 
FROM  
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME  
WHERE 
    tc.CONSTRAINT_TYPE = 'PRIMARY KEY'  
    AND tc.TABLE_NAME = 'table1'