Identity Column & Cascading referential Integrity

Sql Server By Sagar Jaybhay

Identity Column & Cascading referential Integrity

Identity Column by Sagar Jaybhay

If the column is not an identity column then you need to supply the value for that column.

create table tblStudent( Id int identity(1,10),name varchar(22));

In the above query, we have created a tblstudent table where the Id column is the Identity column.

If you see closely

Id Int Identity(1,10)

For the above line, we have an identity(1,10) in this 1 Is Initial Seed and 10 Identity Increment.

Means when you try to insert the first row it will start from 1 and when you insert the second row is incremented by 10.

insert into tblStudent values('Sagar Jaybahy')

insert into tblStudent values('Ram Pawar')

insert into tblStudent values('Arjun Rampal')
Auto Increment Identity Column in SQL Server
Auto Increment Identity Column in SQL Server

The first value starts from 1 and the next value is 11. Here we no need to supply the identity column value.

So what happens when we supply identity column value.

insert into tblStudent values(31,'Arjun Rampal')

By using this query we try to insert value in that table. But it will throw below error

Msg 8101, Level 16, State 1, Line 68 An explicit value for the identity column in table ‘tblStudent’ can only be specified when a column list is used and IDENTITY_INSERT is ON.

So how to supply value to the identity column if it throws an error?

To supply value to identity column use below query

set identity_insert tblStudent ON

Now if you run below query it will be executed successfully

insert into tblStudent(id,name) values(31,'Arjun Rampal').

When you set identity_insert on then you need to always supply the value if you don’t provide the value it will throw below error.

Msg 545, Level 16, State 1, Line 69

The explicit value must be specified for identity column in table ‘tblStudent’ either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

Summary Of Identity Column:

If the column is marked as identity column the values for that column are automatically generated when you insert a new record.

For explicitly supply the value for identity column use below steps:

  1. First, turn on Identity_Insert On
  2. For the insert, a query needs to supply the identity column value.

How to reset all column values of the Identity column?

If you deleted all rows in that table and you want to reset the identity column values you need to use below command

dbcc checkident('tblstudent',reseed,0)

If your table contains any row then by using the above command it will throw a below error.

Checking identity information: current identity value ’31’. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

And if your table is empty then it reseed to value you provided in checident.

How to retrieve Identity Column values in SQL Server?

So how to retrieve the last identity column value, the most common way to get the value is SCOPE_IDENTITY() which is a built-in function. You can also use @@Identity and Ident_current(‘tablename’).

What is the difference between getting an identity values function?

  • Scope_identity()– It is used in the same session and the same scope.
  • @@Identity– it is used in the same session and across any scope
  • Ident_Current(‘tablename’)– it specific table across any session and any scope.
select SCOPE_IDENTITY();

select @@IDENTITY;

select IDENT_CURRENT('tblStudent');
Get Last Indent value
Get Last Indent value

Identity Column & Cascading referential Integrity

Cascading referential Integrity Constraint

This is basically used to define the actions in Microsoft SQL Server should take. Suppose I have 2 tables above Person and tbGender in that person table have foreign key gendered which is the primary key in tbgender table in that.

This means our one table is referring to another table where values in-person table depends on tbgender table.

Then in that case when you try to delete record in tbgender table Microsoft SQL Server throws an Error.

Default actions are marked to None means do nothing. And when you going to delete this record it will throw below error.

Msg 547, Level 16, State 0, Line 43 The DELETE statement conflicted with the REFERENCE constraint “tbl_person_geneder_id_fk”. The conflict occurred in database “temp”, table “dbo.Person”, column ‘genederID’. The statement has been terminated.

How to specify Cascading Referential Integrity?

We have 4 options for that

  1. No Action
  2. Cascade
  3. Set NULL
  4. Set Default

No Action

This is the default behavior. No action specifies that if any attempt made to delete or update a row which is a foreign key referring to another table it will raise an error. And Delete and Update is rolled back.

Cascade

It will specify that when an attempt is made to delete or update a row with key referenced by foreign keys in existing rows in other tables, those keys are also deleted or updated.

Set Null

It will specify that when an attempt is made to delete or update a row with key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are set to NULL.

Set Default

It will specify that when an attempt is made to delete or update a row with key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are set to a default value.

The form where I set the cascading constraint in SQL server?

See below image

set the cascading constraint in SQL server
set the cascading constraint in SQL server

As you see in the above image go to the table view and then click on the keys folder. In that folder, if you created a foreign key then it will show here. Then you need to click on that foreign key name value by doing this above pop up will show then you need to check with insert and update specification where you can find the rule.

Sagar Jaybhay, from Maharashtra, India, is currently a Senior Software Developer. He has continuously grown in the roles that he has held in the more than seven years he has been with this company. Sagar Jaybhay is an excellent team member and prides himself on his work contributions to his team and company as a whole.

Related posts