Re: primary key and unique index

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