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
