SQL Server, can't insert null into primary key field?

Question

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)

I get the following:

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

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

Any ideas!?

Solution 1

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

See: SQL by Design: how to Choose the primary key

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.

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

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)

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)

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:

Solution 2

Primary Key fields cannot contain null values in MS SQL. If you want to populate a SQL table and dont know what to enter for a integer based primary key field then set the pk to an Identity field. Also when specifying Insert statements its wise to use the column mapping portion of the insert statment for example:

Insert into (field1, field2, field3)
values
(value1, value2, value3)

The reason for this is it insures that the column order is what you developed for as a SQL administrator can modify column order. It also allows you to insert a row with an identity Primary key with out specifying the value of the Primary Key Example

CREATE TABLE [dbo].[foo](
    [fooid] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](50) NULL,
 CONSTRAINT [PK_foo] PRIMARY KEY
(
        [fooid] ASC
)

now my insert statement is simple

Insert into foo (name)
values
("John")

the result in the table would be

1, "John"

Solution 3

You probably don't have (you forgot to add) autoincrement set on your integer primary key.

Solution 4

Primary keys shouldnt accept null value.Why you are inserting null values to a primary key field ?Primary key field should have a non-nullable,unique value which will make each of your record in the table unique

Solution 5

I'm assuming your real issue is that you're not sure how to write an insert statement so that the PK is auto populated correct? You need to name the fields you're setting values for, it looks like you're trying to set all of them but just exclude the PK field like so:

INSERT INTO someTable
(fieldName1, fieldName2) 
VALUES(1,1)

Where sometable is a table with three fields. PK, fieldName1, and fieldName2. You also need to make sure that the identity property on the PK field is set to true.

View more solutions

Comments

[ChristopheD]

+1 (definite answer). For completeness sake: unique keys can generally contain null values (primary can't).

[marc_s]

@Nick: shame on MySQL ! This clearly violates ANSI rules on the relational model

[Nick]

For an auto-increment, even if I leave the null out of the insert arguements. I get the same error.

[Nelson Rothermel]

@Nick & @Marc - That doesn't make any sense. If you truly have a primary key and MySQL allows you to insert null values, then you have defeated the point of a primary key. That means you could insert multiple rows with all null values with no way of uniquely identifying each one. I wonder if in MySQL the null gets ignored and it autoincrements. It's like saying you'll give the same ID Number to two people. How will you ID them?

[marc_s]

@Nelson: it seems that inserting a NULL value into the PK column in MySQL will trigger an "auto-numbering" scheme. Rather strange behavior in my opinion........

[Nelson Rothermel]

@marc - Then there's the InterBase/Firebird way, ... VALUES (GEN_ID(SOMEID, 1), "another val". Decouples the ID generating from the table which can be good and bad. It's good because you can use it for other purposes in your stored procedures.

[aaaa bbbb]

As your example shows, it is always a good idea to specify the insert column names.

[Gennady Vanin Геннадий Ванин]

@Nick, concerning "Not true, MySQL does". Can you comment please on comment to stackoverflow.com/questions/3906811/… , affirming that PK in MySQL cannot be NULL?

[user610064]

@Nelson Rothermal I don't see why you shouldn't have an option to have a Null value as a valid primary key value. Paradox allows this. The primary key can still be unique - you can have a Null key field value and still not allow a duplicate key... Treat Null as just another value.

[Nelson Rothermel]

@user610064: Allowing one null value would be fine. Multiple null values would be a problem and I was alluding to that, though I don't remember what I was thinking at the time and why I assumed you would have multiple nulls. Oh well...

[jonnow]

This got me nearly all the way, I couldn't change the Identity Specification in SSMS, believe this was because the PK column was set to "unique identifier". Had to create a new column and delete the old PK (stackoverflow.com/questions/4446596/…) then it worked perfectly.

[marc_s]

@jonnow: yes, the IDENTITY is only available to int or bigint datatypes