英文:
mySQL syntax creating/dropping unique index, using "name" vs "column"
问题
我在MySQL中遇到了一个特殊的边缘情况。我有以下表格：
SHOW TABLE CREATE users;
输出：
users | CREATE TABLE users (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
serial varchar(32) NOT NULL,
version tinyint(4) unsigned NOT NULL COMMENT 'Key Number / version',
PRIMARY KEY (id),
UNIQUE KEY serial (serial, version)
) ENGINE=InnoDB AUTO_INCREMENT=7078 DEFAULT CHARSET=latin1
我无法从语法中理解以下内容：
- 我是否有两个唯一键，一个只在列
serial上，另一个在列(serial, version)上 - 还是说，我在列
(serial, version)上有一个名为serial的唯一键？
当我尝试添加一个具有重复键serial（但版本不同）的行时，有时会允许我，有时会出现duplicate key serial的错误。这不是确定性的。
不管怎样，除了我现在拥有的内容之外，我的目标是只在列(serial, version)上有一个唯一键。所以我尝试运行：
ALTER TABLE users DROP INDEX serial;
令我惊讶的是，这会删除两个唯一键，而不仅仅是一个列，就好像实际上那只是一个名称而不是约束本身。
那么它到底是什么呢？如果那只是一个名称，为什么它不允许我多次使用相同的serial（但不同的version）行呢？我该如何实现这一点？有人可以解释一下如何理解这个吗？
谢谢。
英文:
I'm encountering a particular edge case in mySQL. I have the following table:
SHOW TABLE CREATE users;
Output:
users | CREATE TABLE `users` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`serial` varchar(32) NOT NULL,
`version` tinyint(4) unsigned NOT NULL COMMENT 'Key Number / version',
PRIMARY KEY (`id`),
UNIQUE KEY `serial` (`serial`, `version`)
) ENGINE=InnoDB AUTO_INCREMENT=7078 DEFAULT CHARSET=latin1
I'm not able to understand from syntax whether:
- I have two uniques keys, one only on column
serialand one on columns(serial, version) - Or, I have one unique key on columns
(serial, version), of nameserial
When I try adding a row which a duplicate key serial (but not same version), sometimes it allows me, and sometimes I get an error on duplicate key serial. It's not deterministic.
Anyhow, beside what I have now, my goal is to have just ONE unique key on columns (serial, version). So I tried running:
ALTER TABLE users DROP INDEX serial;
This, to my surprise, removes BOTH unique keys and not just the one columns, as if in fact that was a name and not a constraint itself.
So which is what? If that's only a name, why doesn't it allow me multiple rows with same serial (and different version)? How can I achieve that?
Could someone clarify how to understand this?
Thank you
答案1
得分: 0
从您上面发布的创建表语句中：
您有一个主键，使列'id'唯一，意味着每个记录必须有不同的id。
还有一个名为'serial'的唯一键，使名为'serial'和'version'的列作为组合唯一。
因此，您可以拥有无限数量的记录/行，例如，只要该记录有不同的'serial'分配。
您还可以使用以下代码查看索引的名称：
SHOW INDEXES FROM TABLE;
英文:
From the create table statement you posted above:
You got one primary key, making the column 'id' unique,
meaning every record must have a different id.
And a unique key named 'serial', which makes columns named
'serial' and 'version' unique as a combination.
So you can have infinite records/rows with diffrent versions for example,
as long that record has a different 'serial' assigned.
You can also look up names of your index with
SHOW INDEXES FROM TABLE;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论