question on create table with 2 Identity column one of which is nullable ? RRS feed

  • Question

  • I have a generated DDL that gave me a few tables like this below where there is a intorder column set as the 2nd identity column 

    and it is set to allow nulls , which makes no sense I am wondering if this is just an error of the autogen process or is having 2 identity columns possible? (and can an identity col be nul???)

    -- Creating table 'lkpSWAT5rankLU'
    CREATE TABLE [dbo].[lkpSWAT5rankLU] (
        [id] int IDENTITY(1,1) NOT NULL,
        [Description] varchar(254)  NULL,
        [intorder] int IDENTITY(1,1) NULL

    Monday, June 29, 2020 3:44 PM

All replies

  • There is only one IDENTITY column allowed per table and it must be non-nullable.

    E.g. creating the table like this

    USE tempdb;
    DROP TABLE IF EXISTS [dbo].[lkpSWAT5rankLU];
    CREATE TABLE [dbo].[lkpSWAT5rankLU] (
        [id] INT IDENTITY(1, 1) ,
        [Description] VARCHAR(254) not NULL     
    will create a the ID column as INT NOT NULL.

    Monday, June 29, 2020 3:53 PM