Eduardo Eduardo - 1 year ago 157
SQL Question

Why most SQL databases allow defining the same index twice?

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.

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download