Incorrect INFORMATION_SCHEMA for Cross-Schema Foreign Keys

[waltaskew]

This appears to be a regression. This bug is preventing me from upgrading from 1.5.33 where this appears to work correctly.

The following is output working with 1.5.54 in the provided docker image.

There are a few bugs around retrieving metadata from the information schema for foreign keys that reference unique indexes across schemas.

1. DDL Setup

To reproduce, create a database with the following DDL. It sets up two schemas: a target schema other_schema and a default schema. The default schema has tables that reference a unique index in other_schema.

CREATE SCHEMA `other_schema`;

-- Table in non-default schema with a unique index (non-PK target)
CREATE TABLE other_schema.referenced_table (
    id INT64 NOT NULL,
    unique_val INT64 NOT NULL,
) PRIMARY KEY (id);

CREATE UNIQUE INDEX other_schema.referenced_table_unique_val_idx
ON other_schema.referenced_table (unique_val);

-- Table in default schema referencing the unique index
CREATE TABLE referring_table (
    id INT64 NOT NULL,
    ref_val INT64 NOT NULL,
    CONSTRAINT fk_ref_val FOREIGN KEY (ref_val) REFERENCES other_schema.referenced_table (unique_val) NOT ENFORCED
) PRIMARY KEY (id);

-- A second table referencing the same unique index (triggers duplicate rows bug)
CREATE TABLE referring_table_2 (
    id INT64 NOT NULL,
    ref_val INT64 NOT NULL,
    CONSTRAINT fk_ref_val_2 FOREIGN KEY (ref_val) REFERENCES other_schema.referenced_table (unique_val) NOT ENFORCED
) PRIMARY KEY (id);

---

2. Demonstrated Bugs in INFORMATION_SCHEMA

After creating the tables, query the metadata tables to see the discrepancies.

Bug A: Mismatch in Constraint Name and Schema

TABLE_CONSTRAINTS prefixes the constraint name with the schema and leaves CONSTRAINT_SCHEMA empty, while REFERENTIAL_CONSTRAINTS does not.

-- Query RC
SELECT constraint_name, unique_constraint_schema, unique_constraint_name 
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
WHERE constraint_name = 'fk_ref_val';

Output:

+-----------------+--------------------------+---------------------------------------------------------+
| constraint_name | unique_constraint_schema | unique_constraint_name                                  |
+-----------------+--------------------------+---------------------------------------------------------+
| fk_ref_val      | other_schema             | IDX_referenced_table_unique_val_U_6279FBB6083F67DA      |
+-----------------+--------------------------+---------------------------------------------------------+

-- Query TC
SELECT constraint_schema, constraint_name, table_schema, table_name, constraint_type 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE table_name = 'referenced_table' AND constraint_type = 'UNIQUE';

Output:

+-------------------+-------------------------------------------------------------------------+--------------+------------------+-----------------+
| constraint_schema | constraint_name                                                         | table_schema | table_name       | constraint_type |
+-------------------+-------------------------------------------------------------------------+--------------+------------------+-----------------+
|                   | other_schema.IDX_referenced_table_unique_val_U_6279FBB6083F67DA         | other_schema | referenced_table | UNIQUE          |
|                   | other_schema.IDX_referenced_table_unique_val_U_6279FBB6083F67DA         | other_schema | referenced_table | UNIQUE          |
+-------------------+-------------------------------------------------------------------------+--------------+------------------+-----------------+

Discrepancies:

• TABLE_CONSTRAINTS.CONSTRAINT_SCHEMA is empty string '' instead of 'other_schema'.
• TABLE_CONSTRAINTS.CONSTRAINT_NAME is other_schema.IDX_... (prefixed) but REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_NAME is IDX_... (unprefixed).
• TABLE_CONSTRAINTS contains duplicate identical rows (see Bug C).

---

Bug B: Mismatch in KEY_COLUMN_USAGE Schema

KEY_COLUMN_USAGE also leaves the CONSTRAINT_SCHEMA empty for unique indexes.

SELECT constraint_schema, constraint_name, table_schema, table_name, column_name 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE table_name = 'referenced_table' AND constraint_name LIKE 'IDX_%';

Output:

+-------------------+----------------------------------------------------+--------------+------------------+-------------+
| constraint_schema | constraint_name                                    | table_schema | table_name       | column_name |
+-------------------+----------------------------------------------------+--------------+------------------+-------------+
|                   | IDX_referenced_table_unique_val_U_6279FBB6083F67DA | other_schema | referenced_table | unique_val  |
|                   | IDX_referenced_table_unique_val_U_6279FBB6083F67DA | other_schema | referenced_table | unique_val  |
+-------------------+----------------------------------------------------+--------------+------------------+-------------+

Discrepancy:

• KEY_COLUMN_USAGE.CONSTRAINT_SCHEMA is empty string '' instead of 'other_schema'.

---

Bug C: Duplicate Rows

Having multiple foreign keys referencing the same unique index causes the emulator to duplicate the index representation in TABLE_CONSTRAINTS and KEY_COLUMN_USAGE.

In the outputs above for Bug A and Bug B, notice that there are two identical rows returned for the unique index. This is because both referring_table and referring_table_2 reference it. Adding more referencing tables increases the duplication.

---

3. Impact on Standard Joins

SQLAlchemy (and other tools) reflects foreign keys by joining these tables on catalog, schema, and name:

SELECT rc.constraint_name, tc_uq.table_name
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc_uq
  ON tc_uq.constraint_catalog = rc.unique_constraint_catalog
  AND tc_uq.constraint_schema = rc.unique_constraint_schema
  AND tc_uq.constraint_name = rc.unique_constraint_name
WHERE rc.constraint_name = 'fk_ref_val';

In the emulator, this query returns 0 rows (instead of 1), because:

• tc_uq.constraint_schema ('') != rc.unique_constraint_schema ('other_schema')
• tc_uq.constraint_name ('other_schema.IDX_...') != rc.unique_constraint_name ('IDX_...')