Unique Constraint

Description

The UNIQUE constraint specifies that a group of one or more columns of a table can contain only unique values.

Click here to navigate to the PostgreSQL documentation page for this syntax.

The use of index parameters is not supported by Snowflake.

Grammar Syntax

[ CONSTRAINT constraint_name ]
{UNIQUE ( column_name [, ... ] ) index_parameters}
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

index_parameters are:

[ INCLUDE ( column_name [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]

Sample Source Patterns

PostgreSQL

CREATE TABLE films (
    code        char(5),
    date_prod   date,
    CONSTRAINT production UNIQUE(date_prod) DEFERRABLE
);

CREATE TABLE product (
    code        char(5),
    date_prod   date,
    UNIQUE (date_prod) INITIALLY IMMEDIATE
);

CREATE TABLE person (
    id     integer,
    name    varchar(40),
    UNIQUE(name) WITH (fillfactor=70)
);

Snowflake

CREATE TABLE films (
    code        char(5),
    date_prod   date,
    CONSTRAINT production UNIQUE(date_prod) DEFERRABLE
);

CREATE TABLE product (
    code        char(5),
    date_prod   date,
    UNIQUE (date_prod) INITIALLY IMMEDIATE
);

CREATE TABLE person (
    id     integer,
    name    varchar(40),
    UNIQUE(name)
-- ** MSC-WARNING - MSC-PG0007 - WITH PARAMETER NOT APPLICABLE. CONSTRAINT INDEX PARAMETERS ARE NOT SUPPORTED IN SNOWFLAKE. **
--                 WITH (fillfactor=70)
);

Related EWIs

1. MSC-PG0007: Constraint index parameter not supported.