[SOLVED] SQL : Check if primary key is foreign key

SQL : Check if primary key is foreign key

I want to extract the name of primary key column of table if it is also foreign key from information schema like two tables :

Student 
std_id  std_name

And

PHDstudent
std_id   reaserchfield

That std_id in PHDstudent is primary key and foreign key at same time that refers to Student table.

Solution

Run this query

SELECT Tab.TABLE_NAME, Col.Column_Name from 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col 
WHERE 
    Col.Constraint_Name = Tab.Constraint_Name
    AND Col.Table_Name = Tab.Table_Name
    AND Constraint_Type = 'PRIMARY KEY '
INTERSECT
SELECT Tab.TABLE_NAME, Col.Column_Name from 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col 
WHERE 
    Col.Constraint_Name = Tab.Constraint_Name
    AND Col.Table_Name = Tab.Table_Name
    AND Constraint_Type = 'FOREIGN KEY '