Snowflake ForeignKey constraint ON DELETE not recognised #6472
Description
Search before asking
- I searched the issues and found no similar issues.
What Happened
Whilst attempting to lint some Snowflake SQL that contained an ALTER TABLE to add a Foreign Key constraint with ON DELETE
CREATE TABLE TABLE1(
TABLE1_KEY VARCHAR(255) NOT NULL,
TABLE1 VARCHAR(16) NOT NULL,
TABLE1_TYPE VARCHAR(255) NOT NULL,
TABLE1_FORMAT_ID VARCHAR(255),
CONSTRAINT PK158 PRIMARY KEY (TABLE1_KEY)
)
;
CREATE TABLE REF_TABLE2_FORMAT(
TABLE2_FORMAT_ID VARCHAR(255) NOT NULL,
TABLE2_FORMAT VARCHAR(16) NOT NULL,
CONSTRAINT PK52_1 PRIMARY KEY (TABLE2_FORMAT_ID)
)
;
ALTER TABLE TABLE1 ADD CONSTRAINT REFREF_TABLE1_FORMAT218
FOREIGN KEY (TABLE1_FORMAT_ID)
REFERENCES REF_TABLE2_FORMAT(TABLE2_FORMAT_ID)
ON DELETE NO ACTION
;sqlfluff fails with the following error
== [dummy.sql] FAIL
L: 21 | P: 5 | PRS | Line 21, Position 5: Found unparsable section: 'ON
| DELETE NO ACTION\n;'
WARNING: Parsing errors found and dialect is set to 'snowflake'. Have you configured your dialect correctly?
All Finished 📜 🎉!
Looking at the dialect code the InlineConstraintGrammar does not handle all supported options for Foreign Keys, only Primary/Unique Keys described in
Expected Behaviour
The SQL should lint successfully with zero return code
Observed Behaviour
An error message is produced and the command exits with a non-zero return code
== [dummy.sql] FAIL
L: 21 | P: 5 | PRS | Line 21, Position 5: Found unparsable section: 'ON
| DELETE NO ACTION\n;'
WARNING: Parsing errors found and dialect is set to 'snowflake'. Have you configured your dialect correctly?
All Finished 📜 🎉!
Removing ON DELETE RESTRICT from the constraint allows the SQL to lint successfully
How to reproduce
Create dummy.sql from the following
CREATE TABLE TABLE1(
TABLE1_KEY VARCHAR(255) NOT NULL,
TABLE1 VARCHAR(16) NOT NULL,
TABLE1_TYPE VARCHAR(255) NOT NULL,
TABLE1_FORMAT_ID VARCHAR(255),
CONSTRAINT PK158 PRIMARY KEY (TABLE1_KEY)
)
;
CREATE TABLE REF_TABLE2_FORMAT(
TABLE2_FORMAT_ID VARCHAR(255) NOT NULL,
TABLE2_FORMAT VARCHAR(16) NOT NULL,
CONSTRAINT PK52_1 PRIMARY KEY (TABLE2_FORMAT_ID)
)
;
ALTER TABLE TABLE1 ADD CONSTRAINT REFREF_TABLE1_FORMAT218
FOREIGN KEY (TABLE1_FORMAT_ID)
REFERENCES REF_TABLE2_FORMAT(TABLE2_FORMAT_ID)
ON DELETE NO ACTION
;
execute the following command
sqlfluff lint --dialect snowflake dummy.sql
Dialect
Snowflake
Version
sqlfluff, version 3.2.4
Python 3.11.6
Configuration
.sqlfluff contains
[sqlfluff]
exclude_rules = LT01, LT02, LT05, LT12, LT13, CV06, CP01, RF04
large_file_skip_byte_limit = 0
Are you willing to work on and submit a PR to address the issue?
- Yes I am willing to submit a PR!
Code of Conduct
- I agree to follow this project's Code of Conduct