0

From https://stackoverflow.com/a/51087864/3284469

primary keys can be primary indices.

Must the search key of a primary index be or related to a primary key ? Will the answer be different in PostgreSQL and other DBMS? Thanks.

  • Hi. Indexes are not part of standard SQL & depend on the particular DBMS. What did you learn researching in the manual? What part of that other question is relevant?--please make this & all questions self-contained. – philipxy Jul 04 '18 at 23:49

1 Answers1

0

Postgres doesn't have "primary index", all indexes are implemented the same way, and point directly to the data rows.

Must the search key of a primary index be or related to a primary key

It must be a search on the expression used to form the primary index. if the primary index is constrained to be an index on the primary key then yes else no.

Will the answer be different in PostgreSQL and other DBMS?

yes, because postgresql does not have primary index. although a clustered index is a bit like a primary index. the clustered index can be an index on on any expression, it need not reference the primary key at all.

with postgreql there is no requirement that a table have any index. but if you want to define relations between tables then indexes are required.

Jasen
  • 11,837
  • 2
  • 30
  • 48
  • Mostly agree, but there is no clustered index in PostgreSQL. – Laurenz Albe Jul 02 '18 at 06:33
  • for tables which are not updated subsequent to being clustered there is. – Jasen Jul 02 '18 at 21:11
  • So you'd call an index that is physically in no way different from any other index, but happens to be sorted just like the table, a clustered index? Then you are right of course. – Laurenz Albe Jul 03 '18 at 05:43
  • the clustering index is remembered by the database so that the table can be re-clustered without specifying the index. in that way the index is different from the others. – Jasen Jul 05 '18 at 05:32
  • Thanks for the clarification. – Laurenz Albe Jul 05 '18 at 06:00
  • Thanks. What do you mean by "if you want to define relations between tables then indexes are required."? –  Jul 11 '18 at 19:56
  • `ALTER TABLE ... ADD ... REFERENCES ...` – Jasen Jul 12 '18 at 08:57
  • @Jasen - however, you're not `obliged` to define any indexes on the child field(s). So, the part where you say `if you want to define relations between tables then indexes are required.` would be incorrect - no? – Vérace Aug 13 '22 at 07:19