IDENTITY NOT NULL at Table Creation

sql null identity
Miguel picture Miguel · Jun 1, 2011 · Viewed 17.4k times · Source

Can anyone please tell me whether the instruction IDENTITY NOT NULL at a table creation is redundant or not? I mean, judging by the message

DEFAULT or NULL are not allowed as explicit identity values.

I would say that any column declared as IDENTITY is implicitly also declared as NOT NULL, but I would like to make sure. Can anyone please confirm?

Thank you very much.

Answer

Pranay Rana picture Pranay Rana · Jun 1, 2011

SQL Server adds NOT NULL constraint to identity columns automatically eventhough he did not speficy it when creating a table

Consider the following table script

create table test(id int identity(1,1), name varchar(1000))

Now Generate the script of the table from Management Studio. It generates the script as

CREATE TABLE [dbo].[test](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [varchar](1000) NULL
) ON [PRIMARY]

Eventhough NOT NULL constraint is not specified in the table script by default it is added. The identity column will never be NULL. So NOT NULL constraint is added default

Related questions

How to use NULL or empty string in SQL

I would like to know how to use NULL and an empty string at the same time in a WHERE clause in SQL Server. I need to find records that have either null values or an empty string. Thanks.

Read More
Not equal <> != operator on NULL

Could someone please explain the following behavior in SQL? SELECT * FROM MyTable WHERE MyColumn != NULL (0 Results) SELECT * FROM MyTable WHERE MyColumn <> NULL (0 Results) SELECT * FROM MyTable WHERE MyColumn IS NOT NULL (568 Results)

Read More
NULL values inside NOT IN clause

This issue came up when I got different records counts for what I thought were identical queries one using a not in where constraint and the other a left join. The table in the not in constraint had one null …

Read More