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, the information schema can show each constraint’s table and columns, the order of composite-key columns, and, for foreign keys, the referenced table and columns. The example below uses PostgreSQL; check the documentation for your database engine and version before adapting it.
What primary and foreign keys tell you
Primary keys identify rows
A primary key is a constraint on one column or a group of columns that uniquely identifies each row. In PostgreSQL, its values must be unique and not null. A table can have one primary-key constraint, which may contain multiple columns, or none; PostgreSQL does not require every table to have one. A table may also have other unique constraints. PostgreSQL 18 documentation describes a primary key as a column or group of columns usable as a unique row identifier.
As an Amazon Associate I earn from qualifying purchases.
Foreign keys enforce declared relationships
A foreign key belongs to the referencing table. It requires its values to correspond to a primary or unique key in the referenced table, maintaining 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. Nullable referencing columns can affect whether a row must match.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Column names alone do not establish a database-enforced relationship. A column named customer_id might be intended to refer to a customer table, but only a declared constraint confirms that the database enforces that link.
#1 Best Overall
Composite keys use columns together
When a key contains multiple columns, the combination is the key; no single member necessarily identifies a row. The order of columns is significant in the constraint metadata. For example, an order_items table can reference both orders and products, with a composite key such as (order_id, product_no) preventing duplicate pairs.
Inspect declared keys in PostgreSQL
PostgreSQL’s information_schema.key_column_usage is a useful starting point: it reports columns included in primary-key, foreign-key, and unique constraints, including their ordinal positions. It excludes check constraints and returns only metadata visible to the current user. Therefore, filter by constraint type rather than assuming every returned row is a primary or foreign key.
The following is a query pattern based on the documented view fields; validate it against the PostgreSQL version and schema you use. It joins constraint and column metadata using catalog, schema, constraint, and table identity, rather than relying on a constraint name alone.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →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;In the output, constraint_type distinguishes primary from foreign keys. The table_schema, table_name, and column_name fields identify the constrained side; ordinal_position gives each column’s position within the constraint. For a foreign key, the referenced_table_schema, referenced_table_name, and referenced_column_name fields show the referenced side when available.
Check the referenced columns and foreign-key rules
information_schema.constraint_column_usage identifies the referenced columns for foreign-key constraints. PostgreSQL’s information_schema.referential_constraints provides the associated unique or primary constraint and update and delete rules. Its rows are also privilege-sensitive: the view only shows constraints for which the user has write access to the referencing table. PostgreSQL documentation: constraint_column_usage. PostgreSQL documentation: referential_constraints.
Interpret results without overclaiming
- A unique constraint is not a primary key. The key-column view includes unique, primary-key, and foreign-key columns. Use
table_constraints.constraint_typeto tell them apart. - Read composite keys as a group. Use
ordinal_positionto preserve the column order; do not treat each component as a separate key. - An empty result is not proof that no relationship exists. Metadata visibility depends on privileges, and unconstrained columns may still be related by application logic or convention.
- Use the right engine’s documentation. The information schema is a standard-defined, portability-oriented interface, but implementations differ, and it does not expose PostgreSQL-specific features. These views and query fields are PostgreSQL examples, not universal instructions for every database.
For a visual schema browser, verify whether it distinguishes declared constraints from inferred links, shows composite-column order, displays both sides of a foreign key, and respects the same access limits. The metadata-view approach above establishes declared constraints that the current user can see; it cannot determine a relationship that the database has not declared.
Quick Recap
Best Value
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.
