Why most SQL databases allow defining the same index twice?

Question

Why most SQL databases allow defining the same index (or constraint) twice?

For example in MySQL I can do:

CREATE TABLE testkey(id VARCHAR(10) NOT NULL, PRIMARY KEY(id));

ALTER TABLE testkey ADD KEY (id);

ALTER TABLE testkey ADD KEY (id);

SHOW CREATE TABLE testkey;

CREATE TABLE `testkey` (

  `id` varchar(10) NOT NULL,

  PRIMARY KEY (`id`),

  KEY `id` (`id`),

  KEY `id_2` (`id`)

)

I do not see any use case for having the same index or constraint twice. And I would like SQL databases not allowing me do so.

I also do not see the point on naming indexes or constraints, as I could reference them for deletion just as I created them.

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

I can see that some databases prevent duplicate indexes. Oracle Database prevents duplicate indexes https://www.techonthenet.com/oracle/errors/ora01408.php while other databases like MySQL and PostgreSQL do not have duplicate index prevention.

Why most SQL databases allow defining the same index twice?

Sign up