How to Temporarily disable all foreign key constraints

 While working with MSSQL and Asp.net Core with Entity Framework, many times we have came across a situation where we have to migrate data from one database to newly created database. e.g. Copying all the data from Production DB to Testing DB. 

• Using Entity Framework Code First approach, we create database
• Once database is created, need to copy all the data from MSSQL (Generate script > Dataonly); create Database script for the data
• Now while running the script generated from PROD db to Test DB (there could me some modifications in test db, some columns are added) , we need to disable foreign key constraint and then re-enable foreign key constraints

 

How to disable all the foreign key constraints in MSSQL and then again re-enable all the foreign key constraints in MSSQL 

We can use following scripts

-- Disable all the constraint in database
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

-- Enable all the constraint in database
EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'

This is the most easiest way to Disable and Enable all the Foreign key constraints on MSSQL.

How DBCC CHECKIDENT Works - How to Reset identity seed after deleting records in SQL Server

Scenario I faced

I have inserted records into a SQL Server database table. 

The table had a primary key defined and the auto increment identity seed is set to “Yes”.  So now whenever I do an insert in DB my primary key ID starts with number 1,2,3 and so on.

Now say I have total 100 records and I delete all the records, in this case when I insert new records its primary key ID starts with 101, where as I want it to reset to 1. 

So after deleting all records from SQL Server data table, I wanted to reset identity seed to 0, so my next insert should have primary key as 1,2,3 and so on.

Use following Command to Reset SEED

DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value ]}}]) [ WITH NO_INFOMSGS ]

Example : 

  DBCC CHECKIDENT ('[TableName]', RESEED, 0); GO

Above example will Reset Identity value to initial value i.e. new entry will start from 1

In case we want to Force Identity value to set to Some specific number we use it as follows DBCC 







CHECKIDENT ('[TableName]', RESEED, 10);

So what is the Seed value and when we cannot reset it

The seed value is the value inserted into an identity column for the first row loaded into the table. All subsequent rows contain the current identity value plus the increment value where current identity value is the last identity value generated for the table or view.

You can't use DBCC CHECKIDENT for the following tasks:

• We cannot Change the original seed value specified for an identity column when the table or view was created.

• Cannot be used to Reseed existing rows in a table or view.

To change the original seed value and reseed any existing rows, we need to drop the identity column and recreate it specifying the new seed value. When the table contains data, the identity numbers are added to the existing rows with the specified seed and increment values. The order in which the rows are updated isn't guaranteed.

SqlException: Cannot open database "DATABASE" requested by the login. The login failed. Login failed for user 'IIS APPPOOL\APPPOOLNAME'.

While deploying web based and Mobile based Attendance system on IIS and MSSQL2017, I got following error.

1. Project developed using Entity Framework Core and MVC .net Core 2.2
2. To deploy web application in IIS, I published core 2.2 web application and put it under IIS.
3. Assigned new APP Pool with with No Managed Code for .NET CLR Version.

I got following SQL related error

An unhandled exception occurred while processing the request.

SqlException: Cannot open database "DATABASE" requested by the login. The login failed. Login failed for user 'IIS APPPOOL\apppoolname'.

System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, object providerInfo, string newPassword, SecureString newSecurePassword, bool redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, bool applyTransientFaultHandling, string accessToken)

To resolve this issue, simply create User named 'IIS APPPOOL\apppoolname in SQL

After creating user, assign it to your respective database and give db permissions to that user.

This will resolve the issue.