多列主键中的NULL值 [英] NULL value in multi-column primary key

问题描述

我有一个包含几列组成主键的表.存储的数据的性质允许其中的某些字段具有NULL值.我是这样设计我的桌子的:

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 NULL values. I have designed my table as such:

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;

但是，当我运行describe test时，它显示如下:

However, when I run describe test it shows like this:

|| *Field* || *Type*                || *Null* || *Key* || *Default* || *Extra* 
|| Field1  || smallint(5) unsigned  || NO     || PRI   ||           ||         
|| Field2  || decimal(5,2) unsigned || NO     || PRI   || 0.00      ||         

插入NULL值时，我总是收到错误消息.

And I keep getting an error when inserting a NULL value.

'Field2'列不能为空

Column 'Field2' cannot be null

这是因为作为主键一部分的字段不能为空吗?除了对NULL使用'0'以外，我还有什么选择?

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?

推荐答案

来自MySQL文档:

PRIMARY KEY是唯一索引，其中所有键列必须定义为NOT NULL.如果他们
没有显式声明为NOT NULL，MySQL如此隐式(无声地)声明了它们. 一个表只能有一个PRIMARY KEY. PRIMARY KEY的名称始终为PRIMARY，即 因此不能用作任何其他类型索引的名称.

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.

http://dev.mysql.com/doc/refman/5.1/en/create-table.html

如果Field2可以为NULL，我会问为什么您需要将它作为主键的一部分，因为您随后需要Field1在所有行中都不同.因此，Field1本身就足以作为主键.您可以在Field2上创建其他类型的索引.

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.

这篇关于多列主键中的NULL值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！