为什么大多数SQL数据库允许定义相同的索引两次？ [英] Why most SQL databases allow defining the same index twice?

问题描述

为什么大多数SQL数据库允许两次定义相同的索引（或约束）？

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

例如在MySQL中我可以做：

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`)
)

请参阅具有相同索引或约束两次的任何用例。我希望SQL数据库不允许我这样做。

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.

推荐答案

所有编程语言允许您编写冗余：

All programming languages allow you to write redundancies:

<?php
$foo = 'bar';
$foo = 'bar';

这只是一个例子，你很明显可能有重复的代码，重复的函数或重复的数据结构更浪费。

That's 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 this depends on the situation. Maybe there's a good reason in some rare case to write something that seems redundant. In that case, you'd be just as put out if the technology didn't allow you to do it.

您可能感兴趣的话，您可能会感兴趣在一个名为Maatkit的工具，它是MySQL用户不可缺少的工具的集合。其中一个工具检查重复的键：

You might be interested in a tool called Maatkit, which is a collection of indispensable tools for MySQL users. One of its tools checks for duplicate keys:

http://www.maatkit.org/doc/mk-duplicate-key-checker.html

如果您作为一个MySQL开发人员，新手或者专家，你应该立即下载Maatkit并放下一整天来阅读文档，试用集合中的每个工具，并学习如何将它们集成到你的日常开发任务中。

If you're a MySQL developer, novice or expert, you should download Maatkit right away and set aside a full day to read the docs, try out each tool in the set, and learn how to integrate them into your daily development tasks. You'll kick yourself for not doing it sooner.

至于命名索引，它允许你这样做：

As for naming indexes, it allows you to do this:

ALTER TABLE testkey DROP KEY `id`, DROP KEY `id_2`;

如果没有命名，就无法删除单个索引。你必须删除整个表，并重新创建它没有索引。

If they weren't named, you'd have no way to drop individual indexes. You'd have to drop the whole table and recreate it without the indexes.

这篇关于为什么大多数SQL数据库允许定义相同的索引两次？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！