On DELETE CASCADE not working in snowflake

Question

I have two tables:

CREATE TABLE category(
    id INTEGER NOT NULL DEFAULT CATEGORY_SEQUENCE.NEXTVAL,
    name VARCHAR(50),
    PRIMARY KEY(id)
);

CREATE TABLE product(
    id INTEGER NOT NULL DEFAULT PRODUCT_SEQUENCE.NEXTVAL,
    name VARCHAR(50),
    id_category INTEGER REFERENCES category(id) ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(id)
);

When I DELETE FROM category, the products do not get deleted.

What am I doing wrong?

user_12200988 · Answer

Thing is Snowflake doesn't really service constraints other than not null. It will have them (as in store somewhere) but not really enforce - which means your cascade won't work.

reference: https://docs.snowflake.com/en/sql-reference/constraints-overview.html

On DELETE CASCADE not working in snowflake

1 Answers