get_multi_foreign_keys Returns Columns in Composite Foreign Key Out of Order

Environment details

Programming language: Python
OS: Linux
Language runtime version: 3.12.2
Package version: 1.16.0

Steps to reproduce

Creating a table like:

CREATE TABLE composite_fk (
  a STRING(MAX),
  b STRING(MAX),
  c STRING(MAX),
  CONSTRAINT composite_fk FOREIGN KEY (a, b) REFERENCES composite_pk(a, b),
) PRIMARY KEY (a, b, c);

get_multi_foreign_keys returns referred_columns and constrained_columns in different orders.

I see {"referred_columns": ["b", "a"], "constrained_columns": ["a", "b"]}

This in turn affects alembic auto-generation, because it attempts to drop and recreate the foreign key which appears to have a different order than what I've defined.

#271 reports and #289 offers a fix for the constrained columns, but the referred columns are still effectively unordered.

I think the fundamental problem is that the current SQL used for this relies on the information_schema.constraint_column table for the referred columns, but columns in that table are unordered. They don't have an equivalent for the ordinal_position column in the information_schema.key_column_usage table.

It looks like the intention is that you use the information_schema.referential_constraints table to find the unique constraint associated with the FK's referenced columns and pair it to the referencing columns in the FK itself to get everything in the correct order.

This works, except for one edge case in the emulator. The emulator looks like it incorrectly records information_schema.referential_constraints entries when FKs point across schemas. The referenced table has the wrong schema when the FK is in another schema. So this SQL I think is what we want, but it fails in the emulator (but works with real spanner) for FKs which refer to tables in a different schema:

SELECT
  tc.table_schema,
  tc.table_name,
  tc.constraint_name,
  tcu.table_name,
  tcu.table_schema,
  -- Find the corresponding pairs of columns for the foreign key constraint and its related unique constraint.
  ARRAY(
    SELECT
      (kcu.column_name, pkcu.column_name)
    FROM
      information_schema.key_column_usage AS kcu
    JOIN
      information_schema.key_column_usage AS pkcu
      ON
        pkcu.constraint_catalog = rc.unique_constraint_catalog
        AND pkcu.constraint_schema = rc.unique_constraint_schema
        AND pkcu.constraint_name = rc.unique_constraint_name
        AND pkcu.ordinal_position = kcu.ordinal_position
    WHERE
      kcu.constraint_catalog = tc.constraint_catalog
      AND kcu.constraint_schema = tc.constraint_schema
      AND kcu.constraint_name = tc.constraint_name
    ORDER BY
      kcu.ordinal_position
  )
FROM
  information_schema.table_constraints AS tc
-- Find the foreign key constraint for the referring table.
JOIN
  information_schema.referential_constraints AS rc
  ON
    rc.constraint_catalog = tc.constraint_catalog
    AND rc.constraint_schema = tc.constraint_schema
    AND rc.constraint_name = tc.constraint_name
-- Find the corresponding unique constraint on the referenced table.
JOIN 
  information_schema.table_constraints AS tcu
  ON
    tcu.constraint_catalog = rc.unique_constraint_catalog
    AND tcu.constraint_schema = rc.unique_constraint_schema
    AND tcu.constraint_name = rc.unique_constraint_name
JOIN
  information_schema.tables AS t
  ON
    t.table_catalog = tc.table_catalog
    AND t.table_schema = tc.table_schema
    AND t.table_name = tc.table_name

fix: Return Correct Column Order in get_multi_foreign_keys

fix: Return Correct Column Order in get_multi_foreign_keys (#783)

Navigation Menu

Repository navigation

get_multi_foreign_keys Returns Columns in Composite Foreign Key Out of Order #779

Environment details

Steps to reproduce

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

get_multi_foreign_keys Returns Columns in Composite Foreign Key Out of Order #779

Description

Environment details

Steps to reproduce

Activity

waltaskew commented on Oct 15, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions