MySQL 多列主键中的 NULL 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11001333/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
NULL value in multi-column primary key
提问by simbabque
I've got a table with several columns making up the primary key. The nature of the data stored allows some of these fields to have NULLvalues. I have designed my table as such:
我有一个由几列组成的主键的表。存储的数据的性质允许其中一些字段具有NULL值。我的桌子是这样设计的：
CREATE TABLE `test` (
`Field1` SMALLINT(5) UNSIGNED NOT NULL,
`Field2` DECIMAL(5,2) UNSIGNED NULL DEFAULT NULL,
PRIMARY KEY (`Field1`, `Field2`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;
However, when I run describe testit shows like this:
但是，当我运行时，describe test它显示如下：
|| *Field* || *Type* || *Null* || *Key* || *Default* || *Extra*
|| Field1 || smallint(5) unsigned || NO || PRI || ||
|| Field2 || decimal(5,2) unsigned || NO || PRI || 0.00 ||
And I keep getting an error when inserting a NULLvalue.
我在插入NULL值时不断收到错误消息。
Column 'Field2' cannot be null
列“Field2”不能为空
Is this because a field that is part of a primary key cannot be null? What are my alternatives besides using, say, '0' for NULL?
这是因为作为主键一部分的字段不能为空吗？除了使用 '0' 之外，我还有什么选择NULL？
采纳答案by Girish Rao
From the MySQL documentation :
从 MySQL 文档：
A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL. If they
are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). A table can have only one PRIMARY KEY. The name of a PRIMARY KEY is always PRIMARY, which thus cannot be used as the name for any other kind of index.
PRIMARY KEY 是唯一索引，其中所有键列都必须定义为 NOT NULL。如果它们
没有显式声明为 NOT NULL，MySQL 会隐式地（并且默默地）声明它们。一张表只能有一个 PRIMARY KEY。PRIMARY KEY 的名称始终是 PRIMARY，因此不能用作任何其他类型的索引的名称。
http://dev.mysql.com/doc/refman/5.1/en/create-table.html
http://dev.mysql.com/doc/refman/5.1/en/create-table.html
If Field2 can be NULL, I question why you need it as part of the Primary Key since you then need Field1 to be distinct across all rows. So Field1 by itself should be sufficient as the Primary Key. You could create a different type of index on Field2.
如果 Field2 可以为 NULL，我质疑您为什么需要它作为主键的一部分，因为您需要 Field1 在所有行中都是不同的。所以 Field1 本身应该足以作为主键。您可以在 Field2 上创建不同类型的索引。
回答by Lordferrous
Primary keys are used to make the column both unique and not null
主键用于使列既唯一又不为空
Inorder to insert insert null values make field2 as Unique
为了插入插入空值使 field2 为唯一
Unique constraint make the field removes duplicates but allow nullvalues
唯一约束使字段删除重复项但允许空值
回答by vijay
Primary key states that column mustn't have NULLvalues. So columns used for defining composite primary key isn't going to be NULL.
主键表示列不能有NULL值。所以用于定义复合主键的列不会是NULL.
Also Oracle server compares the combination of all columns used in a composite primary key definition. If your all columns existing data (say x,y) matched with newly adding row, it will raise error of Unique Constraint Violated.
此外，Oracle 服务器会比较复合主键定义中使用的所有列的组合。如果您的所有列现有数据（例如 x，y）与新添加的行匹配，则会引发违反唯一约束的错误。
Moreover,look at this thread: What's wrong with nullable columns in composite primary keys?.
此外，看看这个线程： 复合主键中的可为空列有什么问题？.
This link provides valuable information regarding possibility of NULLABLE columns in composite key!
此链接提供了有关复合键中 NULLABLE 列的可能性的宝贵信息！
回答by Dmitry Kaigorodov
You can use unique key like this:
您可以像这样使用唯一键：
mysql> CREATE TABLE `test` (
-> `Field1` SMALLINT(5) UNSIGNED NOT NULL,
-> `Field2` DECIMAL(5,2) UNSIGNED NULL DEFAULT NULL,
-> UNIQUE KEY (`Field1`, `Field2`)
-> )
-> COLLATE='latin1_swedish_ci'
-> ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> desc test
-> ;
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| Field1 | smallint(5) unsigned | NO | MUL | NULL | |
| Field2 | decimal(5,2) unsigned | YES | | NULL | |
+--------+-----------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
回答by jcho360
you can use unique keys, please take a look to this link, they work with null values
您可以使用唯一键，请查看此链接，它们使用空值
http://www.xaprb.com/blog/2009/09/12/the-difference-between-a-unique-index-and-primary-key-in-mysql/
http://www.xaprb.com/blog/2009/09/12/the-difference-between-a-unique-index-and-primary-key-in-mysql/
