When creating a foreign key which points from one table to a table in another schema, the INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS incorrectly states the location of the referred primary key. It states that the referred primary key is in the same schema as the foreign key & referring table. Below, the UNIQUE_CONSTRAINT_SCHEMA should be blank, not 's'
$ gcloud spanner databases ddl update db --ddl='CREATE TABLE t (id STRING(MAX)) PRIMARY KEY (id)'
Schema updating...done.
$ gcloud spanner databases ddl update db --ddl='CREATE SCHEMA s'
Schema updating...done.
$ gcloud spanner databases ddl update db --ddl='CREATE TABLE s.f (
id STRING(MAX),
t_id STRING(MAX),
CONSTRAINT fk_s_f_t_id FOREIGN KEY (t_id) REFERENCES t(id)
) PRIMARY KEY (id)'
Schema updating...done.
$ gcloud spanner databases execute-sql db --sql='SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS'
CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME UNIQUE_CONSTRAINT_CATALOG UNIQUE_CONSTRAINT_SCHEMA UNIQUE_CONSTRAINT_NAME MATCH_OPTION UPDATE_RULE DELETE_RULE SPANNER_STATE
s fk_s_f_t_id s PK_t SIMPLE NO ACTION NO ACTION COMMITTED
It appears the constraint is enforced correctly after some very brief testing, it's just not represented accurately in the information schema.
This is a difference in behaviour from real-Spanner -- it correctly reports the UNIQUE_CONSTRAINT_SCHEMA as '' in this case.
When creating a foreign key which points from one table to a table in another schema, the
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTSincorrectly states the location of the referred primary key. It states that the referred primary key is in the same schema as the foreign key & referring table. Below, theUNIQUE_CONSTRAINT_SCHEMAshould be blank, not 's'It appears the constraint is enforced correctly after some very brief testing, it's just not represented accurately in the information schema.
This is a difference in behaviour from real-Spanner -- it correctly reports the
UNIQUE_CONSTRAINT_SCHEMAas''in this case.