Re: primary key and unique index

Many thanks Phil for complementary information .

Le sam. 24 mars 2018 à 09:53, HORDER Phil <Phil(dot)Horder(at)uk(dot)thalesgroup(dot)com>
a écrit :

> Some databases will create a unique index for you when you create a
> primary key.
>
> Oracle will create one, but only if you haven’t already done that.
>
>
>
> Postgres will ALWAYS create a unique index based on the primary key – so
> you should never do that as well, or the db will have to maintain two
> identical indexes.
>
>
>
> (When we migrated our db design from Oracle we ended up with lots of
> duplicate indexes).
>
>
>
> SQL> Select * from pg_indexes order by schemaname, tablename;
>
>
>
> Phil Horder
>
> Database Mechanic
>
>
>
> Thales
>
> Land and Air Systems
>
> Horizon House, Throop Road, Templecombe, Somerset, BA8 0DH, UK
>
> www.thalesgroup.com/uk
>
>
>
> Tel: +44 (0) 1963 37 2041
>
> Email: phil(dot)horder(at)uk(dot)thalesgroup(dot)com
>
>
>
> *Please consider the environment before printing a hard copy of this
> email.*
>
>
>
> The information contained in this e-mail is confidential. It is intended
> only for the stated addressee(s) and access to it by any other person is
> unauthorised. If you are not an addressee, you must not disclose, copy,
> circulate or in any other way use or rely on the information contained in
> this e-mail. Such unauthorised use may be unlawful. If you have received
> this e-mail in error, please inform us immediately on +44 (0)1963 370511
> and delete it and all copies from your system.
>
>
>
> *Thales UK Limited. A company registered in England and Wales. Registered
> Office: 350 Longwater Avenue, Green Park, Reading, RG2 6GF. Registered
> Number: 868273*
>
>
>
> *From:* Thomas Poty [mailto:thomas(dot)poty(at)gmail(dot)com]
> *Sent:* 23 March 2018 07:56
> *To:* pgsql-general(at)lists(dot)postgresql(dot)org
> *Subject:* primary key and unique index
>
>
>
> 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.
>
> 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
>