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.
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
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