iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
To find a database’s declared primary and foreign keys, inspect its constraint metadata—not column names such as id or product_id. In PostgreSQL, information_schema views can show each constraint’s type, columns, and (for foreign keys) referenced table. The example below is PostgreSQL-specific; check the documentation for your database engine and version before adapting it.
What primary and foreign keys tell you
Primary key: the row identifier
A primary key is one column or a group of columns that uniquely identifies each row. PostgreSQL requires primary-key values to be unique and non-null. A table can have one primary-key constraint, or none; it may also have additional unique constraints. As the PostgreSQL documentation puts it, “A primary key constraint indicates that a column, or group of columns, can be used as a unique identifier for rows in the table.” PostgreSQL 18: Constraints.
Foreign key: the enforced relationship
A foreign key is defined on the referencing table. Its column or columns must match a primary key or unique key on the referenced table, which lets the database enforce referential integrity. For example, in orders.product_no REFERENCES products(product_no), orders is the referencing table and products is the referenced table. Under ordinary foreign-key behavior, a non-null value in orders.product_no must match a referenced product key.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Column names alone do not establish that relationship. A column called customer_id might be intended to reference another table, but only a declared constraint provides the database-enforced relationship described here.
#1 Best Overall
Composite keys and unique constraints
A key can consist of multiple columns. With a composite key, the columns identify a row together, and their order in metadata matters when reconstructing the constraint. A UNIQUE constraint is not automatically a primary key: PostgreSQL’s key_column_usage view includes columns belonging to primary-key, foreign-key, and unique constraints, so check the constraint type rather than guessing from a name.
Inspect declared keys in PostgreSQL
For a quick inventory, start with information_schema.key_column_usage. It reports constraint, table, column, and ordinal-position details for accessible primary, foreign, and unique constraints. It does not include check constraints. To distinguish primary from foreign keys and show the referenced side, join it to table_constraints and constraint_column_usage.
Rank #2
SELECT
tc.constraint_type,
kcu.constraint_name,
kcu.table_schema,
kcu.table_name,
kcu.column_name,
kcu.ordinal_position,
ccu.table_schema AS referenced_table_schema,
ccu.table_name AS referenced_table_name,
ccu.column_name AS referenced_column_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_catalog = kcu.constraint_catalog
AND tc.constraint_schema = kcu.constraint_schema
AND tc.constraint_name = kcu.constraint_name
AND tc.table_schema = kcu.table_schema
AND tc.table_name = kcu.table_name
LEFT JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_catalog = tc.constraint_catalog
AND ccu.constraint_schema = tc.constraint_schema
AND ccu.constraint_name = tc.constraint_name
AND ccu.table_schema = tc.table_schema
AND ccu.table_name = tc.table_name
WHERE tc.constraint_type IN ('PRIMARY KEY', 'FOREIGN KEY')
ORDER BY kcu.table_schema, kcu.table_name, tc.constraint_type,
kcu.constraint_name, kcu.ordinal_position;This query is an illustrative PostgreSQL pattern based on documented information-schema fields, not a tested query. Validate it against your PostgreSQL version and schema. The joins include catalog, schema, constraint, and table identity because constraint names alone may not uniquely identify a constraint in every context.
Free tools Windows power users keep installed
One-click scans. No signup required.
Read the result
constraint_typedistinguishesPRIMARY KEYfromFOREIGN KEY.table_schema,table_name, andcolumn_nameidentify the local, or referencing, side.ordinal_positionpreserves column order within a multi-column constraint. Treat all its columns as the key together.- The
referenced_table_*andreferenced_column_namefields identify the other end when the constraint is a foreign key. They are drawn fromconstraint_column_usage.
For additional foreign-key details, PostgreSQL’s referential_constraints view reports the associated unique or primary constraint and update/delete rules. Its visibility is narrower: it shows constraints where the current user has write access to the referencing table.
Understand what the metadata can—and cannot—show
Information-schema results depend on the privileges of the current user. If a constraint is missing from the output, first check whether that user can see the relevant table and constraint; an empty result is not proof that no constraint exists. Metadata also describes declared constraints, not relationships inferred from naming conventions or application code. A schema may have a logical relationship that has not been declared as a foreign key.
The information schema is a standards-defined, portability-oriented interface, but it does not expose every PostgreSQL-specific feature. Other database engines and versions may differ in view names, columns, behavior, or visibility rules. Treat the query as a PostgreSQL example and consult the relevant engine’s documentation when adapting it.
Use key metadata to understand table relationships
Suppose an order can contain several products and each product can appear in many orders. A bridge table such as order_items can hold foreign keys to both orders and products. A composite key such as (order_id, product_no) can prevent duplicate order-product pairs. Inspecting both ends of each declared foreign key reveals the enforced links; it does not establish whether every intended application-level relationship has been constrained.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Metadata query or visual schema browser?
A database client’s schema browser may be convenient for exploring a diagram, while metadata queries are useful when you need a repeatable, inspectable inventory. Whichever route you use, check what it actually displays:
Quick Recap
- Does it show only declared constraints, or also inferred relationships?
- Can you see the order of columns in composite keys?
- Are both referencing and referenced columns identified?
- Do your current privileges expose all relevant tables and constraints?
- Is the approach supported by your particular database engine and version?
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
