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

Answer 1

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"

Answer 2

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:

Answer 3

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

Answer 4

you can use 0 instead of null for only 1 unique row, null is not possible for PK. Or you can omit PK and use and auto increament PK field

Answer 5

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

Answer 6

Assuming you have an autoincrement field for your primary Key you'll need to include the field list on your insert and not put a value for that field e.g.

INSERT INTO qcRawMatTestCharacteristic 
(Answer1,Answer2,...SomeDateField)
VALUES(1,1,1,1,1,1,1,'','','', GETDATE(), 1)

Answer 7

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.

Answer 8

if you have an identity column, you don't need to specify it in the insert statement.

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

However, if you have a primary key that isn't an identity column, then you do need to specify it, because otherwise it'll try to insert a null and primary keys by default are non-nullable.