[sqlalchemy-spanner]: get_unique_constraints() splits multi-column unique constraints into separate single-column entries #18836

Determine this is the right repository

• I determined this is the correct repository in which to report this bug.

Summary of the issue

SpannerDialect.get_unique_constraints() returns $N$ separate single-column constraint dictionaries for an $N$-column UNIQUE constraint instead of a single constraint dictionary containing all $N$ column names in ordinal order.

Environment details

• OS: Linux
• Python version: 3.13
• sqlalchemy-spanner version: 1.17.2 (also reproduced on main / 1.20.1 at eb8a76628adf10093959600071a18f50285c2ecf)
• sqlalchemy version: 2.0.52

Problem description

In SpannerDialect.get_unique_constraints:

        sql = """
SELECT ccu.CONSTRAINT_NAME, ccu.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu
    USING (TABLE_CATALOG, TABLE_SCHEMA, CONSTRAINT_NAME)
WHERE
    tc.TABLE_NAME="{table_name}"
    AND tc.TABLE_SCHEMA="{table_schema}"
    AND tc.CONSTRAINT_TYPE = "UNIQUE"
    AND tc.CONSTRAINT_NAME IS NOT NULL
""".format(table_schema=schema or "", table_name=table_name)

        cols = []
        with connection.connection.database.snapshot() as snap:
            rows = snap.execute_sql(sql)

            for row in rows:
                cols.append({"name": row[0], "column_names": [row[1]]})

        return cols

There are two bugs here when a table has a multi-column UNIQUE constraint (for example, the backing UNIQUE constraint Spanner creates in INFORMATION_SCHEMA.TABLE_CONSTRAINTS for a composite FOREIGN KEY (a, b) REFERENCES parent (col_b, col_a)):

1. No grouping by constraint name: Every row in CONSTRAINT_COLUMN_USAGE appends a separate {"name": row[0], "column_names": [row[1]]} dict, so a 2-column unique constraint produces two UniqueConstraint objects with the same name and 1 column each instead of one UniqueConstraint with 2 columns.
2. Missing column ordinal ordering: INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE has no ORDINAL_POSITION column. Preserving the constraint's declared column order requires joining INFORMATION_SCHEMA.KEY_COLUMN_USAGE and ordering by ORDINAL_POSITION (as get_multi_pk_constraint and get_multi_foreign_keys already do).

Suggested fix

Join INFORMATION_SCHEMA.KEY_COLUMN_USAGE and aggregate columns per constraint with ARRAY_AGG(KCU.COLUMN_NAME ORDER BY KCU.ORDINAL_POSITION) (or implement get_multi_unique_constraints and delegate get_unique_constraints to it, matching get_columns, get_pk_constraint, get_foreign_keys, and get_indexes):

SELECT
  TC.TABLE_NAME,
  TC.CONSTRAINT_NAME,
  ARRAY_AGG(KCU.COLUMN_NAME ORDER BY KCU.ORDINAL_POSITION) AS COLUMN_NAMES
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
  USING (TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME)
WHERE
  TC.TABLE_SCHEMA = @schema
  AND TC.CONSTRAINT_TYPE = 'UNIQUE'
  AND TC.CONSTRAINT_NAME IS NOT NULL
GROUP BY TC.TABLE_NAME, TC.CONSTRAINT_NAME

Reproduction steps: code

import unittest.mock as mock
from google.cloud.sqlalchemy_spanner.sqlalchemy_spanner import SpannerDialect

dialect = SpannerDialect()
conn = mock.MagicMock()
snap = conn.connection.database.snapshot.return_value.__enter__.return_value
# Simulate INFORMATION_SCHEMA rows for a 2-column unique constraint uq_ab on (col_b, col_a)
snap.execute_sql.return_value = [
    ("uq_ab", "col_b"),
    ("uq_ab", "col_a"),
]

actual = dialect.get_unique_constraints(conn, "my_table")
print("Actual:  ", actual)
print("Expected:", [{"name": "uq_ab", "column_names": ["col_b", "col_a"]}])

Actual output

Actual:   [{'name': 'uq_ab', 'column_names': ['col_b']}, {'name': 'uq_ab', 'column_names': ['col_a']}]

Expected output

Expected: [{'name': 'uq_ab', 'column_names': ['col_b', 'col_a']}]