0
Started

Column Sequence gets added to child FK Column (Snowflake)

Erick Hanftwurzel 2 months ago updated by hannah gryska 2 months ago 1

If I define a sequence on a PK column and create an FK with it, the child table will add a default in it's DDL. In the example below, TABLE_2.COL_1 has a default value. It shouldn't inherit the default value.

Image 2155

CREATE OR REPLACE TABLE TABLE_1
(
COL_1 number NOT NULL DEFAULT SEQ_COL_1.nextval,
INSERT_TS timestamp NOT NULL, -- From template: "ETL_METADATA"
LAST_UPDATE_TS timestamp, -- From template: "ETL_METADATA"
CONSTRAINT PK_TABLE_1 PRIMARY KEY ( COL_1 )
);

CREATE OR REPLACE TABLE TABLE_2
(
COL_1 number NOT NULL DEFAULT SEQ_COL_1.nextval,
INSERT_TS timestamp NOT NULL, -- From template: "ETL_METADATA"
LAST_UPDATE_TS timestamp, -- From template: "ETL_METADATA"
CONSTRAINT FK_31 FOREIGN KEY ( COL_1 ) REFERENCES TABLE_1 ( COL_1 )
);
Started

Hello Erick, 

Thank you for contacting Support. Our team is still investigating this, but it does indeed look like you've found a bug. We will keep you updated on the progress of both the bug and the fix. Thank you again for your help in uncovering this!