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')
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:
- First, turn on Identity_Insert On
- 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
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');
Identity Column & Cascading referential Integrity
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
- No Action
- Set NULL
- Set Default
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.
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.
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.
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
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.