SQL Server，不能在主键字段中插入空值? [英] SQL Server, can't insert null into primary key field?

问题描述

我正准备在这个上撕掉我的头发.我是 MS SQL 的新手，还没有在任何地方看到过类似的帖子.

I'm about ready to rip my hair out on this one. I'm fairly new to MS SQL, and haven't seen a similar post anywhere.

当我尝试做这样的陈述时:

When I try to do a statement like this:

INSERT INTO qcRawMatTestCharacteristic 
VALUES(NULL, 1,1,1,1,1,1,1,'','','', GETDATE(), 1)

我得到以下信息:

不能插入 NULL 值列iRawMatTestCharacteristicId"，桌子'Intranet.dbo.qcRawMatTestCharacteristic';列不允许空值.插入失败.

Cannot insert the value NULL into column 'iRawMatTestCharacteristicId', table 'Intranet.dbo.qcRawMatTestCharacteristic'; column does not allow nulls. INSERT fails.

我理解错误，但空值是我的主字段的 int 数据类型.

I understand the error, but the null value is for my my primary field with an int data type.

有什么想法！?

推荐答案

任何 关系数据库中的主键都不允许为 NULL - 这是主键的主要基本特征之一.

Primary keys in any relational database are not allowed to be NULL - it's one of the main, fundamental characteristics of a primary key.

参见:SQL by Design:如何选择主键

从不为空
主键值不能为空，也不能做任何事情将主键呈现为空.这是不可侵犯的关系规则ANSI 支持的模型，关系数据库管理系统(RDBMS) 设计，以及 SQL Server.

Never Null
No primary key value can be null, nor can you do anything to render the primary key null. This is an inviolate rule of the relational model as supported by ANSI, of relational database management system (RDBMS) design, and of SQL Server.

更新:好的，所以您需要 SQL Server 中的自动增量"主键.

UPDATE: ok, so you want an "auto-increment" primary key in SQL Server.

您需要在 CREATE TABLE 语句中将其定义为 INT IDENTITY:

You need to define it as an INT IDENTITY in your CREATE TABLE statement:

 CREATE TABLE dbo.YourTable(ID INT IDENTITY, col1 INT, ..., colN INT)

然后当您执行 INSERT 时，您需要明确指定要插入的列，但不要在该列表中指定ID"列 - 然后 SQL Server 将自动处理查找正确的值:

and then when you do an INSERT, you need to explicitly specify the columns to insert, but just don't specify the "ID" column in that list - then SQL Server will handle finding the proper value automagically:

 INSERT INTO dbo.YourTable(col1, col2, ..., colN) -- anything **except** `ID`      
 VALUES(va1l, val2, ..., valN)

如果您想在已经创建表后执行此操作，您可以在 SQL Server Management Studio 的表设计器中执行此操作:

If you want to do this after having created the table already, you can do so in the SQL Server Management Studio's table designer:

这篇关于SQL Server，不能在主键字段中插入空值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！