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

70,103

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:

alt text

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.

Share:
70,103
Nick
Author by

Nick

Updated on July 09, 2022

Comments

  • Nick
    Nick almost 2 years

    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!?

  • ChristopheD
    ChristopheD over 13 years
    +1 (definite answer). For completeness sake: unique keys can generally contain null values (primary can't).
  • marc_s
    marc_s over 13 years
    @Nick: shame on MySQL ! This clearly violates ANSI rules on the relational model
  • Nick
    Nick over 13 years
    For an auto-increment, even if I leave the null out of the insert arguements. I get the same error.
  • Nelson Rothermel
    Nelson Rothermel over 13 years
    @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
    marc_s over 13 years
    @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
    Nelson Rothermel over 13 years
    @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
    aaaa bbbb over 13 years
    As your example shows, it is always a good idea to specify the insert column names.
  • Gennady Vanin Геннадий Ванин
    Gennady Vanin Геннадий Ванин over 13 years
    @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
    user610064 over 11 years
    @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
    Nelson Rothermel over 11 years
    @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
    jonnow about 7 years
    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
    marc_s about 7 years
    @jonnow: yes, the IDENTITY is only available to int or bigint datatypes