数据库设计:可选,但如果提供值,则必须是唯一的

时间:2020-12-25 11:10:19

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:

我的一个表格里有一列。它是可选的,所以可以为空。但是,如果为该列提供了一个值,则它必须是惟一的。两个问题:

  1. How do I implement this in my database design (I'm using MySQL Workbench, by the way)
  2. 如何在数据库设计中实现这一点(顺便说一下,我使用的是MySQL Workbench)
  3. Is there a potential problem with my model?
  4. 我的模型有潜在的问题吗?

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 NULLs 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 NULLs 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)是的。键和键的依赖关系是关系数据库设计中数据完整性的基础。如果一个属性应该是唯一的,那么它应该作为一个键来实现。可空的“键”根本不是键,而且无论如何也不是必需的,因为它总是可以被移动到一个新表中,并且在不丢失任何信息的情况下使其成为不可空的。