CODEWITHSUNDEEP
postgresql
Lee Chengkai
Lee Chengkai

Reputation: 165

Does column order matter when defining unique constraints

How is this

CREATE TABLE foo (
    id   SERIAL PRIMARY KEY,
    col1 VARCHAR(50) NOT NULL,
    col2 VARCHAR(50) NOT NULL,
    col3 DOUBLE PRECISION NULL,
    UNIQUE(col1, col2)
);

Different from this?

CREATE TABLE foo (
    id   SERIAL PRIMARY KEY,
    col1 VARCHAR(50) NOT NULL,
    col2 VARCHAR(50) NOT NULL,
    col3 DOUBLE PRECISION NULL,
    UNIQUE(col2, col1) -- reversed column ordering
);

From what I understand both commands will generate an index on the two columns to enforce the unique constraint but with different ordering.

So I would not need to generate a separate index to speed up queries like this in either case.

SELECT id, col3 FROM foo WHERE col1 = 'stack' AND col2 = 'overflow'

However if future queries will also involve querying by column "col2" alone like below the latter form is preferred because the index will still be usable right?

SELECT id, col3 FROM foo WHERE col2 = 'overflow'

Upvotes: 12

Views: 4066

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

The order matters if you expect to ever use the index as a partial index (documentation). For example, suppose you had a unique index on (col1, col2), and you wanted to optimize the following query:

SELECT col1, col2 FROM foo WHERE col1 = 'stack';

The index on (col1, col2) could still be used here, because col1, which appears in the WHERE clause, is the leftmost portion of the index. Had you defined the unique constraint on (col2, col1), the index could not be used for this query.

Upvotes: 23

Related Questions