Re: primary key and unique index

From: Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: primary key and unique index
Date: 2018-03-23 09:23:13
Message-ID: c3e4185d-19c5-623a-d00f-37f2069b5b5c@matrix.gatewaynet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 23/03/2018 09:55, Thomas Poty wrote:
> Hi all,
>
> I am migrating fromMySQL to Postgresql 9.6.
>
> In MySQL a  "show create table" gives me :
>  ...
>   PRIMARY KEY (`ID`,`CountryCode`,`LanguageCode`),
>   UNIQUE KEY `unique_my table_4` (`ID`,`CountryCode`,`LanguageCode`),
> ...
>
> So, In PostgreSQL, does it make sense to create a primary key AND a unique index based on the same columns?
> Is PostgreSQL smart enough to use the unique index created for the primary key.
>

This is redundant. However, IMO it is always beneficial to have an bigint PK, set implicitly via a sequence.
So you could have smth like :

pkid BIGSERIAL PRIMARY KEY,
.......
UNIQUE KEY unique_my table_4 ("ID","CountryCode","LanguageCode"),
...
This way, you get the artificial bigint PK (pkid), and the explicit natural unique key which enforces your business integrity.

> I know PostgreSQL can be based on a unique index to create a primary key but I also know it is possible to create several indexes on the same columns with the same order.
>
> Thanks
>
> Thomas

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Patricia DECHANDOL 2018-03-23 10:55:45 Use pgloader with FDW
Previous Message Alessandro Aste 2018-03-23 08:31:26 Re: Postgresql 10.3 , query never completes if LIMIT clause is specified and paralle processing is on