Two tables, an obvious relationship

CREATE TABLE customers (customer_id INT PRIMARY KEY, name TEXT);
CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT, total NUMERIC);

orders.customer_id obviously refers to customers.customer_id. Every join between these two tables treats that as true, every report built on top of them assumes it, and in real, everyday use, it holds. The question worth asking is whether the database is actually enforcing that relationship, or whether it just happens to be true so far because nobody's ever inserted data that breaks it.

Asking the database directly, not assuming

SQLAlchemy's reflection API can answer this without reading a single line of the schema's original CREATE TABLE statements:

from sqlalchemy import inspect

insp = inspect(engine)
for table in ["customers", "orders"]:
    fks = insp.get_foreign_keys(table)
    print(f"{table}: {fks if fks else 'no declared foreign keys'}")

customers: no declared foreign keys
orders: no declared foreign keys

Neither table has an actual, enforced foreign key. orders.customer_id is a plain integer column, as far as the database itself is concerned, with no more real connection to customers than any other unrelated number.

What "no enforcement" actually allows

conn.execute(text(
    "INSERT INTO orders VALUES (102, 99, 75.00)"
))

real insert with a non-existent customer_id succeeded (no FK to stop it)

Customer 99 doesn't exist anywhere in the customers table. The insert succeeded anyway, because nothing in the schema says it shouldn't. The consequence shows up the moment anything joins on that column:

conn.execute(text("""
    SELECT o.order_id, o.customer_id, c.name
    FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id
""")).fetchall()

[(102, 99, None)]

A real order, with a real, silently orphaned reference. Any report joining orders to customer names now has a row with no name, and depending on how that report handles a missing value, that's either an obviously blank row someone notices, or a row that quietly gets dropped by an inner join instead of a left join, disappearing from a total without anyone realizing a real order went missing from it.

The fix, and confirming it actually holds

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id)
REFERENCES customers (customer_id);

Then the exact same bad insert, run again:

conn.execute(text(
    "INSERT INTO orders VALUES (103, 99, 20.00)"
))

psycopg.errors.ForeignKeyViolation: insert or update on table "orders"
violates foreign key constraint "fk_orders_customer"

Now it's a real, immediate failure at insert time, not a silent gap discovered later by whoever happens to run the report that surfaces it.

Why this gap is common, not rare

Nobody sets out to skip a foreign key on purpose. It happens through an early migration that didn't include it, a table created quickly during a prototype that never got the constraint added once the prototype became real, or an ORM's auto-generated schema that only declares what the application code explicitly told it to. The tables work correctly regardless, right up until they don't, and there's no error anywhere in that gap to notice, only correct-looking behavior for as long as every insert happens to stay well-behaved.

The takeaway

A join working correctly is not the same claim as a relationship being enforced, and the difference between the two is invisible until something inserts data that violates the assumption nobody declared as a rule. Reflection is a real, direct way to check which one is actually true for any table you're relying on but didn't personally create: ask the database what it's actually enforcing, rather than trusting that a column's name describes a guarantee the schema never made.