多列主键中的 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 文档:

From the MySQL documentation :

主键

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

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.

https://dev.mysql.com/doc/refman/8.0/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屋！