Why do most SQL databases allow you to define the same index twice?
Why do most SQL databases allow the same index (or constraint) to be specified 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 see no point in using the same index or constraint twice. And I would like the SQL databases not to let me do this.
I also see no point in specifying indices or constraints, as I could reference them for deletion the same way I created them.
source to share
I see that some databases prevent duplicate indexes. Oracle database prevents duplicate indexes https://www.techonthenet.com/oracle/errors/ora01408.php whereas other databases like MySQL and PostgreSQL do not have duplicate index.
source to share
All programming languages allow you to write redundancy:
<?php
$foo = 'bar';
$foo = 'bar';
This is just an example, you could obviously have duplicate code, duplicate functions, or duplicate data structures that are much more wasteful.
It's up to you to write good code, and it depends on the situation. Maybe on rare occasions there is a good reason to write something extra. In this case, you will also be extinguished if the technology does not allow you to do it.
You may be interested in Maatkit, which is a collection of indispensable tools for MySQL users. One of his tools checks for duplicate keys:
http://www.maatkit.org/doc/mk-duplicate-key-checker.html
Whether you're a MySQL developer, novice, or expert, you should download Maatkit straight away and set aside a full day to read the docs, try each tool in the suite, and learn how to integrate them into your day-to-day job development. You will kick yourself for not doing this earlier.
As far as naming indices are concerned, this allows you to do this:
ALTER TABLE testkey DROP KEY `id`, DROP KEY `id_2`;
If they weren't named, you wouldn't be able to drop individual indexes. You will have to drop the entire table and recreate it without indexes.
source to share
Several reasons come to mind. In the case of a database product that supports multiple types of indexes, you might want to have the same field or combination of fields indexed multiple times, with each index being of a different type depending on the intended use. For example, some (perhaps most) database products have a tree index that is good for both direct searches (eg KEY_FIELD = 1) and range scans (eg KEY_FIELD> 0 AND KEY_FIELD <5). In addition, some (but definitely not all) database products also support hashed index type, which is only useful for direct searches, but very fast (for example, for comparisons such as KEY_FIELD = 1, but which cannot be used for range comparisons ).If you need to have very fast forward search times but still need to provide for ranked comparisons, it can be helpful to create both a tree index and a hashed index.
Some database products do not allow you to have multiple primary key constraints on a table. However, it may take more effort on the part of the database vendor to prevent possible duplicates than is justified. In the case of an open source database, mainstream developers might think that if a given feature is a big enough deal for a given user, it should be up to that user to submit a code fix to enable any feature that is wanted. Open source is not a euphemism for "I am using your open source product, so now you are my subordinate and must do every function I ever wanted!"
In the end, I think it's fair to say that a product intended for use by software developers can take it for granted, that the user should be expected to be reasonably careful when using the product.
source to share
You shouldn't be in a scenario where you have so many indexes on a table that you can't just quickly look up and see if there is an index there.
As far as name and index constraints go, I am only actually naming constraints. I'll call the constraint FK_CurrentTable_ForeignKeyedColumn so things become more visible when you quickly scan through their lists.
source to share
Since the databases supporting coverage indexes are Oracle, MySQL, SQL Server ... (but not PostgreSQL, oddly enough). Coverage index means indexing two or more columns and is processed from left to right for that list of columns to use them.
So, if I define a coverage index for columns 1, 2 and 3 - my queries should use at least column 1 to use the index. The next possible combination is columns 1 and 2 and finally 1,2 and 3.
What about my queries that only use column 3? Without the other two columns, the coverage index cannot be used. This is the same problem for using only column 2 ... Anyway, a situation where I will consider separate indexes on columns 2 and 3.
source to share