in this article, I will show you how to temporarily disable triggers and database constraints and reseed the identity keys. These techniques are sometimes needed when performing bulk database updates or redesigning the database.

Table of Contents

What are Database Constraints?

Database constraints enforce data integrity. Constraints are a set of rules that can be applied to a column or a table. The rules can enable the data to be checked before any records are committed to the database. If the transaction is enabled and new transactional data violates these rules, the transaction will be automatically rolled back and not committed to the database.

What are Database Triggers

Triggers are event-driven stored procedures used when complex validation logic is necessary on one or more tables. They can enforce complex business rules and invoke other logic, such as stored procedures. Unlike constraints, since the validation rules are enforced when an event occurs, additional logic is often necessary to roll back the transaction if the data violates the triggers' business rules. Some databases, such as Postgres, also use triggers to enforce constraints.

Why Would you Want to Disable Database Constraints and Triggers?

While constraints are essential to enforce referential integrity, and triggers may be used to implement additional business rules, they can make it challenging to perform bulk operations, such as the initial data seeding for a set of tables. When seeding a group of tables with data, the performance of bulk inserts may also be severely degraded as the additional business rules will consume extra resources.

Additionally, complex logic may be necessary to implement sequential CRUD logic against various tables to adhere to the rules embedded in the constraints or database triggers, and it may be challenging to determine the required crud logic. When making database design changes and seeding new data, it may make sense to temporarily disable database constraints and triggers.

I use Object Relational Mapping (ORM). One disadvantage of ORM is that all indexes, constraints, and triggers are immediately placed into the database, even in the initial design phase. Typically, database developers will iteratively hone the database design. When they are comfortable, they will apply the constraints and triggers at the end of the design phase—unfortunately, using ORM does not allow you to do this.  

What is a Database Identity?

Depending on the database vendor, database identities or auto-increments are synonyms for a primary key with an auto-generated sequence. These keys are often used as foreign keys for other tables. These sequences generally start from 1 and are incrementally updated. However, changes are frequently necessary in the database design phase, and it can be helpful to reset the initial identity, especially when truncating the data inside the tables. 

A Word of Caution

As stated, database constraints and triggers are essential to enforce database integrity. They are there for a reason, and if you are not the database owner or don't know what you're doing, you should not alter the database. You should also not alter a production database. If you make a mistake, you could crash the entire database.

Examples

In the following examples, replace the Users table with your table name.

Microsft SQL Server

Disable all Constraints on a Single Table

Use the following SQL statement to disable the enforcement of constraints on a single table:

ALTER TABLE Users NOCHECK CONSTRAINT ALL

Enable All Constraints on a Single Table

Once you're done, re-enable the table constraints using:

ALTER TABLE Users CHECK CONSTRAINT ALL

Disable All Database Constraints

To disable all constraints, you can use the undocumented sp_MSforeachtable stored procedure. While this is not officially documented, it is a well-known stored procedure used to perform various operations on all MS SQL Server tables.

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL";
// You can also use:
EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"

Enable All Database Constraints

To enable the constraints for the entire database, use:

EXEC sp_msforeachtable "ALTER TABLE ? CHECK CONSTRAINT ALL";
// You can also use:
EXEC sp_MSforeachtable @command1="ALTER TABLE ? CHECK CONSTRAINT ALL"

Disable All Database Triggers

To disable triggers on the entire database, use:

sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all'
// or...
sp_msforeachtable @command1='ALTER TABLE ? DISABLE TRIGGER all'

Reset Identity Keys on a Table

To reset the identity key and delete data on a single table, use the following (note: you don't need to delete the data to reseed the identity):

DBCC CHECKIDENT ('[Users]', RESEED, 0);
GO
DELETE FROM Users;

We can also use the sp_MSforeachtable stored procedure to reset the identities of all tables using:

EXEC sp_MSforeachtable 'DBCC CHECKIDENT(''?'', RESEED, 1)'
// or...
EXEC sp_MSforeachtable @command1 = 'DBCC CHECKIDENT(''?'', RESEED, 1)'

There are other approaches to reseed all of the tables, among other helpful scripts using the MSforeachtable stored procedure found at SqlServerCentral

MySql

You Can't Disable Constraints on a Particular Table Using MySql

Unfortunately, you can't disable constraints on a single table with MySql at this time. However, you can temporarily disable and re-enable all database constraints (see below). 

Disable All Database Constraints

To disable constraints for the entire MySql Database use:

SET FOREIGN_KEY_CHECKS=0;

Enable All Database Constraints

To enable constraints for the entire MySql Database use:

SET FOREIGN_KEY_CHECKS=1;

MySql Does Not Have a Native Function to Disable or Enable Triggers

Unfortunately, MySQL has no native method for enabling or disabling triggers. Depending on your needs, you may need to delete and recreate the triggers or write extra logic to determine if the trigger should be enabled. See MySQL—how to simulate enable/disabled triggers for potential workarounds (note: I have never used these techniques and can't vouch for them). 

Delete all Table Records and Reseed the Identity

When truncating tables with MySql, the identity seed of the primary key (if your table has one) will be reset to 1.

TRUNCATE Users; -- Truncate will set the auto-increment to 1

Manually Setting the Auto Increment on a MySql Table

If you want to set the identity seed manually, use auto_increment like so:

ALTER TABLE Users AUTO_INCREMENT = 1  -- Replace Users with your own table

Postgres

Unlike most other databases, Postgres uses triggers to enforce the database constraints. Turning off these triggers will enable you to perform operations that violate the constraints. 

Disable All Triggers and Constraints for a Single Postgres Table

ALTER TABLE Users disable trigger all; -- Replace Users with your own table

Deferring Enforcement of a Postgres Constraint Until the End of a Transaction

Use the following statement to defer enforcement of a given constraint until the end of a database transaction:

ALTER TABLE Users ALTER CONSTRAINT UserId_fkey DEFERRABLE INITIALLY DEFERRED; -- Replace Users with the name of your table. Also, replace UserId_fKey with the name of your constraint

Disable All Postgres Table Constraints 

To disable all table constraints, use:

ALTER TABLE Users DISABLE TRIGGER ALL; -- Replace Users with the name of your own table

Disable Postgres Triggers for the Current Session

If you are using Postgres 8.3 or higher, you can also use session replication roles, which control the replication-related triggers and rules for the current session. The advantage of using replication is that this only disables the triggers and constraints for the current session.

To disable the triggers for the current session, use:

SET session_replication_role to replica;

Reenable Postres Triggers for the Current Session

To re-enable the triggers, use:

SET session_replication_role = 'origin';

Delete All Table Records and Reseed Table Identity

To delete all records and reset the identity seed, use:

TRUNCATE TABLE Users RESTART IDENTITY -- Replace Users with your own table

Oracle

Disabling a Single Oracle Constraint

There is no way to disable all of the constraints in Oracle with a single command. Instead, it would be best if you disabled each table-based constraint, one at a time like so:

ALTER TABLE Users DISABLE CONSTRAINT constraint_name; /* Replace Users with your own table. Also, you must specify the unique constraint name */

Disable All Oracle Table Constraints Using the Cascade Keyword

Using the CASCADE keyword, you can disable all primary and foreign key constraints for a given table. However, you can't use the cascade argument to re-enable the constraints! 

ALTER TABLE Users DISABLE PRIMARY KEY CASCADE; /* Replace Users with your own table name */

However, you can use a PL/Script to enable and disable Oracle constraints. I will not elaborate as I am not currently using Oracle, but you can find example scripts in the links below.

https://stackoverflow.com/questions/128623/disable-all-table-constraints-in-oracle

Disable an Individual Oracle Trigger

You can disable an individual trigger using:

ALTER TRIGGER triggerName DISABLE; /* Replace triggerName with the name of the trigger */

Enabling an Individual Oracle Trigger

To enable an Oracle trigger, use the following PL/SQL statement:

ALTER TRIGGER triggerName ENABLE;  /* Replace triggerName with the name of the trigger */

https://stackoverflow.com/questions/51470/how-do-i-reset-a-sequence-in-oracle

Reseeding Oracle Sequences

To reset Oracle sequences on a table, you can use an undocumented PL/SQL script to reset regular sequences:

alter sequence serial restart start with 1;

To reset Oracle sequences that contain primary or foreign keys, use:

alter table table_name modify id generated by default on null as identity(start with 1);

According to Jon Heller, since Oracle  18c, this feature has appeared in the official Oracle Database Administrator's Guide.

Reseeding Multiple Oracle Sequences 

While it is impossible to disable or enable all Oracle sequences, you can do this using a complicated PL/Script. Oracle expert Tom Kyte discusses how to achieve this on the asktom.oracle.com site

Further Reading