In the first part of this series on IDENTITY columns, I had mentioned that IDENTITY columns must be NOT NULL and must not have a DEFAULT constraint defined on them. Often, this is interpreted in haste as IDENTITY columns cannot have any constraints defined on them.

This is a myth. Today’s post will attempt to clear out this confusion.

Defining a DEFAULT constraint on an IDENTITY column – Msg 1754

The script below is quite simple – it attempts to create a table with an IDENTITY column and then goes on to use the ALTER TABLE statement to add a default constraint to the IDENTITY column.

USE tempdb;
GO

SET NOCOUNT ON;

--1. Prepare the environment
--   Create a table, and add some test data into it

--Safety Check
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

--Create a table, this time WITHOUT a Clustered Key
CREATE TABLE dbo.tIdentity (RecordId INT IDENTITY (1,1),
                            IdentityValue VARCHAR(20)
                           );
GO

--2. Add a default constraint
ALTER TABLE dbo.tIdentity
    ADD CONSTRAINT df_DefaultRecordId DEFAULT(0) FOR RecordId;
GO

--3. Cleanup
IF OBJECT_ID('tIdentity','U') IS NOT NULL
    DROP TABLE dbo.tIdentity;
GO

Executing this script returns an error as shown below:

Msg 1754, Level 16, State 0, Line 3
Defaults cannot be created on columns with an IDENTITY attribute. Table ‘tIdentity’, column ‘RecordId’.
Msg 1750, Level 16, State 0, Line 3
Could not create constraint. See previous errors.

This example settles one part of the puzzle – IDENTITY columns cannot have DEFAULT constraints on them.

Defining other constraints (CHECK/PRIMARY KEY) on an IDENTITY column

The script below creates a table with an IDENTITY column and defines a primary key constraint on the IDENTITY column. Later, the script uses the ALTER TABLE statement to add a CHECK constraint on the IDENTITY value.

Because the DDLs execute successfully, we will insert some test data to test that the constraints are indeed in effect.

USE tempdb ;
GO

SET NOCOUNT ON ;

--1. Prepare the environment
--   Create a table, and add some test data into it

--Safety Check
IF OBJECT_ID('tIdentity', 'U') IS NOT NULL 
    DROP TABLE dbo.tIdentity ;
GO

--Create a table, with a primary key constraint on the IDENTITY column
CREATE TABLE dbo.tIdentity
    (
      RecordId INT IDENTITY(1, 1),
      IdentityValue VARCHAR(20),
      CONSTRAINT pk_tIdentityRecordId PRIMARY KEY CLUSTERED ( RecordId )
    ) ;
GO

--2. Alter the table to add a CHECK constraint
--   Now, we have two constraints on the IDENTITY column:
--   a. A Primary Key constraint
--   b. A Check constraint
ALTER TABLE dbo.tIdentity
ADD CONSTRAINT chk_MaxRecordId CHECK ( RecordId <= 5 ) ;
GO

--2. Add some test data
--   Since we are adding 5 records, the statement will succeeed
INSERT  INTO dbo.tIdentity ( IdentityValue )
VALUES  ( 'One' ),
        ( 'Two' ),
        ( 'Three' ),
        ( 'Four' ),
        ( 'Five' ) ;
GO

--2b.One may also want to check the value of IDENT_CURRENT
PRINT 'CurrentIdentityValue (Before Constraint violation): '
    + CAST(IDENT_CURRENT('dbo.tIdentity') AS VARCHAR(10)) ;
GO

--3. Add some more test data
--   This will fail, because the identity value will now exceed the values 
--   allowed by the CHECK constraint
INSERT  INTO dbo.tIdentity ( IdentityValue )
VALUES  ( 'Six' ) ;
GO

--3b.One may also want to check the value of IDENT_CURRENT
PRINT 'CurrentIdentityValue (After Constraint violation): '
    + CAST(IDENT_CURRENT('dbo.tIdentity') AS VARCHAR(10)) ;
GO

--4. Cleanup
IF OBJECT_ID('tIdentity', 'U') IS NOT NULL 
    DROP TABLE dbo.tIdentity ;
GO

The resulting messages are shown below:

CurrentIdentityValue (Before Constraint violation): 5
Msg 547, Level 16, State 0, Line 5
The INSERT statement conflicted with the CHECK constraint “chk_MaxRecordId”. The conflict occurred in database “tempdb”, table “dbo.tIdentity”, column ‘RecordId’.
The statement has been terminated.
CurrentIdentityValue (After Constraint violation): 6

As can be seen from the resulting messages shown above, the CHECK constraint on the IDENTITY column was in effect resulting in the constraint violation error.

Observation!

Observe that the current value of the IDENTITY column (obtained via IDENT_CURRENT) has incremented even though the value was not inserted. This is because of the failed insert, which can leave holes in the IDENTITY sequence.

Conclusion

As we can see from the examples above, IDENTITY columns cannot have a DEFAULT constraint defined on them – other constraints can still be defined on an IDENTITY column.

Until we meet next time,

Be courteous. Drive responsibly.