Snowflake创建带外键引用的表时如何使用会话变量

设置会话级变量指定数据库和架构名称时，USE DATABASE与USE SCHEMA语句能正常解析变量，但在创建表的外键约束时，用IDENTIFIER($DB_VAR).IDENTIFIER($SCHEMA_VAR).ABC_TABLE(ID)的写法会报错。相关代码如下：

SET (DB_VAR, SCHEMA_VAR) = ('XYZ_DB','XYZ_SCHEMA');

USE DATABASE IDENTIFIER($DB_VAR);
USE SCHEMA IDENTIFIER($SCHEMA_VAR);


create or replace TABLE TEST_VAR
( 
    PARENT_ID NUMBER NOT NULL
    , CHILD_ID NUMBER
    , primary key (PARENT_ID)
    , foreign key (CHILD_ID) references IDENTIFIER($DB_VAR).IDENTIFIER($SCHEMA_VAR).ABC_TABLE(ID) 
);


create or replace view TEST_VAR AS SELECT * FROM IDENTIFIER($DB_VAR).IDENTIFIER($SCHEMA_VAR).TEST_VAR;

Snowflake的外键约束REFERENCES子句不支持通过多个IDENTIFIER()函数拼接库、架构和表名的方式解析变量，这种变量解析语法仅在表/列直接引用等有限SQL上下文中生效，外键目标对象的引用不兼容该写法。

提供两种可行的解决方式：

方式一：拼接完整表路径变量后引用 #

先把库、架构和表名拼接成完整的对象路径，存储到新的会话变量中，再用单个IDENTIFIER()解析这个变量：

SET (DB_VAR, SCHEMA_VAR) = ('XYZ_DB','XYZ_SCHEMA');
-- 拼接完整的表对象路径
SET FULL_ABC_TABLE_PATH = CONCAT($DB_VAR, '.', $SCHEMA_VAR, '.ABC_TABLE');

USE DATABASE IDENTIFIER($DB_VAR);
USE SCHEMA IDENTIFIER($SCHEMA_VAR);

create or replace TABLE TEST_VAR
( 
    PARENT_ID NUMBER NOT NULL
    , CHILD_ID NUMBER
    , primary key (PARENT_ID)
    , foreign key (CHILD_ID) references IDENTIFIER($FULL_ABC_TABLE_PATH)(ID) 
);

方式二：利用当前会话上下文简化引用 #

既然已经通过USE DATABASE和USE SCHEMA切换到目标库和架构的上下文，外键直接引用同上下文内的表即可，无需指定全路径：

SET (DB_VAR, SCHEMA_VAR) = ('XYZ_DB','XYZ_SCHEMA');

USE DATABASE IDENTIFIER($DB_VAR);
USE SCHEMA IDENTIFIER($SCHEMA_VAR);

create or replace TABLE TEST_VAR
( 
    PARENT_ID NUMBER NOT NULL
    , CHILD_ID NUMBER
    , primary key (PARENT_ID)
    , foreign key (CHILD_ID) references ABC_TABLE(ID) 
);

另外注意：你的视图创建语句中，视图名和表名都是TEST_VAR，这会导致对象冲突，建议修改视图名称（比如改为TEST_VAR_VW），同时因为已经切换了上下文，视图也可以简化写法：

create or replace view TEST_VAR_VW AS SELECT * FROM TEST_VAR;

内容的提问来源于stack exchange，提问作者Priya d