Question

Write an algorithm to find the primary key and foreign key(s)?

(Every programming language is accepted.)

Answer 1

querry to find Primary Key and Foreign Key is

SELECT ac.table_name,
column_name,
position,
ac.constraint_name,
DECODE (constraint_type, 'P', 'Primary Key', 'Foreign Key') key_type,
(SELECT ac2.table_name
FROM all_constraints ac2
WHERE AC2.CONSTRAINT_NAME = AC.R_CONSTRAINT_NAME)
fK_to_table
FROM all_cons_columns acc, all_constraints ac
WHERE acc.constraint_name = ac.constraint_name
AND acc.table_name = ac.table_name
AND CONSTRAINT_TYPE IN ('P', 'R')
--AND ac.table_name = 'ACCOUNT' (your table here)
ORDER BY table_name, constraint_type, position;

Algorithm

Select TABLE_NAME   

select COLUMN_NAME   

select POSITION   

select CONSTRAINT_NAME

KEY_TYPE

FK_TO_TABLE

ACCOUNT ACCOUNT_ID 1 ACCOUNT_PK Primary Key   
ACCOUNT ACCOUNT_TYPE_ID 1 ACCOUNT_FK3 Foreign Key ACCOUNT_TYPE
ACCOUNT RELATED_ACCOUNT_ID 1 ACCOUNT_FK5 Foreign Key ACCOUNT
ACCOUNT SERVICE_DB_ID 1 ACCOUNT_FK1 Foreign Key SERVICE_DATABASE
ACCOUNT IMPLEMENTING_AGENCY_ID 1 ACCOUNT_FK4 Foreign Key IMPLEMENTING_AGENCY