Snowflake Primary key using alter statement

Question

when I was adding primary key to snowflake table I saw something weird.

When I ran a query

ALTER TABLE "TESTSCHEMA".table1 ADD PRIMARY KEY (ID);

Above query works as expected. But when I tried to run

ALTER TABLE IF EXISTS "TESTSCHEMA".table1 ADD PRIMARY KEY (ID);

Query was returning error error line 0 at position 0 invalid identifier 'TOK_IF_EXISTS'

Is this a bug or Am I doing something wrong?

Answer 1

Adding a primary key to the same column is expected to result in an error as provided below.

create table "TESTSCHEMA".table (id number); --Statement executed successfully.

ALTER TABLE "TESTSCHEMA".table ADD PRIMARY KEY (ID); --Statement executed successfully.

ALTER TABLE "TESTSCHEMA".table ADD PRIMARY KEY (ID); --SQL compilation error: primary key already exists for table 'TABLE'

create table "TESTSCHEMA"."table1" (id number); --Statement executed successfully.

ALTER TABLE "TESTSCHEMA"."table1" ADD PRIMARY KEY (ID); --Statement executed successfully.

ALTER TABLE "TESTSCHEMA"."table1" ADD PRIMARY KEY (ID); ---SQL compilation error: primary key already exists for table 'table1'

The test case shared by you with the error details can be reported to Snowflake for validation

Answer 2

According to docs, it looks like a bug. Could you raise snowflake support case so that the behaviour can be validated? Thanks,

https://docs.snowflake.com/en/sql-reference/sql/alter-table.html