I have a column in one of my tables. It's optional, so it can be left blank. However, if a value is provided for that column, it must be unique. Two questions:
我的一个表格里有一列。它是可选的,所以可以为空。但是,如果为该列提供了一个值,则它必须是惟一的。两个问题:
- How do I implement this in my database design (I'm using MySQL Workbench, by the way)
- 如何在数据库设计中实现这一点(顺便说一下,我使用的是MySQL Workbench)
- Is there a potential problem with my model?
- 我的模型有潜在的问题吗?
5 个解决方案
#1
4
Just use a UNIQUE
index on the column. See:
只需在列上使用唯一索引。看到的:
http://dev.mysql.com/doc/refman/5.1/en/create-index.html
http://dev.mysql.com/doc/refman/5.1/en/create-index.html
A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.
唯一索引创建一个约束,使索引中的所有值必须是不同的。如果尝试添加具有与现有行匹配的键值的新行,则会发生错误。对于所有引擎,唯一索引允许包含空值的列有多个空值。如果在唯一索引中为列指定前缀值,那么列值必须在前缀中是唯一的。
#2
2
It's can be null (and not blank) and unique. By default value can be null. There is no problem for me.
它可以是空的(而不是空的)和唯一的。默认值可以为空。对我来说没有问题。
#3
1
You can create a UNIQUE index on a table. In MySQL workbench that is the UQ checkbox when creating/editing the table.
您可以在表上创建一个惟一的索引。在MySQL workbench中,这是创建/编辑表时的UQ复选框。
#4
0
Step 1, ALTER
the table and MODIFY
the field so NULL
s are allowed.
步骤1,修改表并修改字段,使之允许为null。
ALTER TABLE my_table MODIFY my_field VARCHAR(100) NULL DEFAULT NULL;
Step 2, add a UNIQUE
index on the field.
步骤2,在字段中添加唯一索引。
ALTER TABLE my_table ADD UNIQUE INDEX U_my_field (my_field);
It's fine to use -- my only hesitation with putting a UNIQUE
index on a nullable field it that it is slightly counter-intuitive at first glance.
使用它很好——我唯一的犹豫是在一个可空字段上放置一个唯一的索引,乍一看它有点反直觉。
#5
0
1) Move the column to a new table, make it unique and non-nullable. You can now have a row in this new table only when you have a value for it.
1)将列移动到新表中,使其惟一且不可为空。现在,只有当这个新表有值时,才可以在这个新表中有一行。
2) Yes. Keys and dependencies on keys are the basis of data integrity in a relational database design. If an attribute is supposed to be unique then it should be implemented as a key. A nullable "key" is not a key at all and anyway is never necessary because it can always be moved to a new table and made non-nullable without loss of any information.
2)是的。键和键的依赖关系是关系数据库设计中数据完整性的基础。如果一个属性应该是唯一的,那么它应该作为一个键来实现。可空的“键”根本不是键,而且无论如何也不是必需的,因为它总是可以被移动到一个新表中,并且在不丢失任何信息的情况下使其成为不可空的。
#1
4
Just use a UNIQUE
index on the column. See:
只需在列上使用唯一索引。看到的:
http://dev.mysql.com/doc/refman/5.1/en/create-index.html
http://dev.mysql.com/doc/refman/5.1/en/create-index.html
A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.
唯一索引创建一个约束,使索引中的所有值必须是不同的。如果尝试添加具有与现有行匹配的键值的新行,则会发生错误。对于所有引擎,唯一索引允许包含空值的列有多个空值。如果在唯一索引中为列指定前缀值,那么列值必须在前缀中是唯一的。
#2
2
It's can be null (and not blank) and unique. By default value can be null. There is no problem for me.
它可以是空的(而不是空的)和唯一的。默认值可以为空。对我来说没有问题。
#3
1
You can create a UNIQUE index on a table. In MySQL workbench that is the UQ checkbox when creating/editing the table.
您可以在表上创建一个惟一的索引。在MySQL workbench中,这是创建/编辑表时的UQ复选框。
#4
0
Step 1, ALTER
the table and MODIFY
the field so NULL
s are allowed.
步骤1,修改表并修改字段,使之允许为null。
ALTER TABLE my_table MODIFY my_field VARCHAR(100) NULL DEFAULT NULL;
Step 2, add a UNIQUE
index on the field.
步骤2,在字段中添加唯一索引。
ALTER TABLE my_table ADD UNIQUE INDEX U_my_field (my_field);
It's fine to use -- my only hesitation with putting a UNIQUE
index on a nullable field it that it is slightly counter-intuitive at first glance.
使用它很好——我唯一的犹豫是在一个可空字段上放置一个唯一的索引,乍一看它有点反直觉。
#5
0
1) Move the column to a new table, make it unique and non-nullable. You can now have a row in this new table only when you have a value for it.
1)将列移动到新表中,使其惟一且不可为空。现在,只有当这个新表有值时,才可以在这个新表中有一行。
2) Yes. Keys and dependencies on keys are the basis of data integrity in a relational database design. If an attribute is supposed to be unique then it should be implemented as a key. A nullable "key" is not a key at all and anyway is never necessary because it can always be moved to a new table and made non-nullable without loss of any information.
2)是的。键和键的依赖关系是关系数据库设计中数据完整性的基础。如果一个属性应该是唯一的,那么它应该作为一个键来实现。可空的“键”根本不是键,而且无论如何也不是必需的,因为它总是可以被移动到一个新表中,并且在不丢失任何信息的情况下使其成为不可空的。