Why can't two tables have an index of the same name?

Question

I'm creating indexes on two separate tables in the same DB (PostgreSQL), and I got an error saying that an index already exists. This was true, however, the index existed on a different table. After changing the name, it worked.

I'm wondering why this is the case? Why are databases designed such that two tables can't have the same name of an index?

I found two sources that answer this, although the answers are different. One is for MySQL, the other is for Postgres:

In postgres how do I add index to existing table?

Same index name for two tables

Answer 1

You can have two indexes of the same name. They just can't be in the same schema. Just like you can have two tables of the same name, but not in the same schema.

sandbox=# create schema test;
CREATE SCHEMA
sandbox=# create table public.a (a_id integer not null);
CREATE TABLE
sandbox=# create table test.a (a_id integer not null);
CREATE TABLE
sandbox=# create index a_idx on public.a (a_id);
CREATE INDEX
sandbox=# create index a_idx on test.a (a_id);
CREATE INDEX

This reflects a decision by the PostgreSQL designers. SQL standards don't deal with creating indexes.

Answer 2

Because the index is stored in the table pg_class. Within that table there is a composite key comprised of the index name and the namespace, which is why you cannot have two indexes with the same name belonging to the same namespace.