Error cannot truncate a table referenced in a foreign key constraint

Why doesn't a TRUNCATE on mygroup work? Even though I have ON DELETE CASCADE SET I get: ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (mytest.instance, CONST...

Why doesn’t a TRUNCATE on mygroup work?
Even though I have ON DELETE CASCADE SET I get:

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (mytest.instance, CONSTRAINT instance_ibfk_1 FOREIGN KEY (GroupID) REFERENCES mytest.mygroup (ID))

drop database mytest;
create database mytest;
use mytest;

CREATE TABLE mygroup (
   ID    INT NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE=InnoDB;

CREATE TABLE instance (
   ID           INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   GroupID      INT NOT NULL,
   DateTime     DATETIME DEFAULT NULL,

   FOREIGN KEY  (GroupID) REFERENCES mygroup(ID) ON DELETE CASCADE,
   UNIQUE(GroupID)
) ENGINE=InnoDB;

Waqleh's user avatar

Waqleh

9,5318 gold badges64 silver badges102 bronze badges

asked Mar 27, 2011 at 21:57

user391986's user avatar

user391986user391986

28.6k39 gold badges123 silver badges200 bronze badges

0

Yes you can:

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE table1;
TRUNCATE table2;

SET FOREIGN_KEY_CHECKS = 1;

With these statements, you risk letting in rows into your tables that do not adhere to the FOREIGN KEY constraints.

Insane Skull's user avatar

Insane Skull

9,1859 gold badges45 silver badges63 bronze badges

answered Nov 10, 2011 at 3:02

user447951's user avatar

12

You cannot TRUNCATE a table that has FK constraints applied on it (TRUNCATE is not the same as DELETE).

To work around this, use either of these solutions. Both present risks of damaging the data integrity.

Option 1:

  1. Remove constraints
  2. Perform TRUNCATE
  3. Delete manually the rows that now have references to nowhere
  4. Create constraints

Option 2: suggested by user447951 in their answer

SET FOREIGN_KEY_CHECKS = 0; 
TRUNCATE table $table_name; 
SET FOREIGN_KEY_CHECKS = 1;

duan's user avatar

duan

8,3253 gold badges48 silver badges70 bronze badges

answered Mar 27, 2011 at 22:03

zerkms's user avatar

zerkmszerkms

246k67 gold badges434 silver badges534 bronze badges

25

I would simply do it with :

DELETE FROM mytest.instance;
ALTER TABLE mytest.instance AUTO_INCREMENT = 1;

Insane Skull's user avatar

Insane Skull

9,1859 gold badges45 silver badges63 bronze badges

answered Sep 24, 2014 at 11:05

George G's user avatar

George GGeorge G

7,25412 gold badges45 silver badges59 bronze badges

6

Easy if you are using phpMyAdmin.

Just uncheck Enable foreign key checks option under SQL tab and run TRUNCATE <TABLE_NAME>

enter image description here

answered Nov 5, 2018 at 12:29

Ajmal Salim's user avatar

Ajmal SalimAjmal Salim

4,0332 gold badges32 silver badges40 bronze badges

1

Tested on MYSQL Database

Solution 1:

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE table1;

Solution 2:

DELETE FROM table1;
ALTER TABLE table1 AUTO_INCREMENT = 1;
TRUNCATE table1;

This works for me. I hope, this will help you also. Thanks for asking this question.

answered Nov 13, 2019 at 11:37

Kamlesh's user avatar

KamleshKamlesh

4,63136 silver badges45 bronze badges

3

you can do

DELETE FROM `mytable` WHERE `id` > 0

answered May 13, 2017 at 13:20

Ali Sadran's user avatar

Ali SadranAli Sadran

2642 silver badges4 bronze badges

5

As per mysql documentation, TRUNCATE cannot be used on tables with foreign key relationships. There is no complete alternative AFAIK.

Dropping the contraint still does not invoke the ON DELETE and ON UPDATE.
The only solution I can ATM think of is to either:

  • delete all rows, drop the foreign keys, truncate, recreate keys
  • delete all rows, reset auto_increment (if used)

It would seem TRUNCATE in MySQL is not a complete feature yet (it also does not invoke triggers).
See comment

answered Oct 16, 2012 at 9:20

SlimDeluxe's user avatar

SlimDeluxeSlimDeluxe

7238 silver badges18 bronze badges

2

While this question was asked I didn’t know about it, but now if you use phpMyAdmin you can simply open the database and select the table(s) you want to truncate.

  • At the bottom there is a drop down with many options. Open it and select Empty option under the heading Delete data or table.
  • It takes you to the next page automatically where there is an option in checkbox called Enable foreign key checks. Just unselect it and press the Yes button and the selected table(s) will be truncated.

Maybe it internally runs the query suggested in user447951’s answer, but it is very convenient to use from phpMyAdmin interface.

user4157124's user avatar

user4157124

2,72013 gold badges26 silver badges42 bronze badges

answered Dec 22, 2016 at 11:41

Rolen Koh's user avatar

Rolen KohRolen Koh

7192 gold badges11 silver badges21 bronze badges

0

Answer is indeed the one provided by zerkms, as stated on Option 1:

Option 1: which does not risk damage to data integrity:

  1. Remove constraints
  2. Perform TRUNCATE
  3. Delete manually the rows that now have references to nowhere
  4. Create constraints

The tricky part is Removing constraints, so I want to tell you how, in case someone needs to know how to do that:

  1. Run SHOW CREATE TABLE <Table Name> query to see what is your FOREIGN KEY‘s name (Red frame in below image):

    enter image description here

  2. Run ALTER TABLE <Table Name> DROP FOREIGN KEY <Foreign Key Name>. This will remove the foreign key constraint.

  3. Drop the associated Index (through table structure page), and you are done.

to re-create foreign keys:

ALTER TABLE <Table Name>
ADD FOREIGN KEY (<Field Name>) REFERENCES <Foreign Table Name>(<Field Name>);

answered Nov 24, 2016 at 19:15

Peyman Mohamadpour's user avatar

0

Another workaround is delete all rows in the table then reset auto-increment columns:

delete from table_name where 1

then Run:

ALTER TABLE table_name AUTO_INCREMENT = 1

answered Jun 3, 2021 at 6:02

mwafi's user avatar

mwafimwafi

3,8268 gold badges55 silver badges79 bronze badges

How to truncate a foreign key constrained table?
This illustration will demonstrate how to solve mysql error when truncating a table with foreign key constraint.
If you are using PHPMYADMIN, it is very easy to truncate a table with foreign key constraint.

  1. Login to PHPMYADMIN and click the table you want to truncate.
  2. Then go to SQL tab Place your code to truncate the table in the SQL
    Editor example truncate table students; Replace students with the
    name of the table.
  3. At the bottom of the editor untick the «Enable foreign key checks» checkbox as shown below:

enter image description here

It will work like magic.

answered Jan 18, 2022 at 18:02

P.Githinji's user avatar

P.GithinjiP.Githinji

1,34910 silver badges5 bronze badges

Just use CASCADE

TRUNCATE "products" RESTART IDENTITY CASCADE;

But be ready for cascade deletes )

answered Mar 25, 2019 at 12:41

Alexus1024's user avatar

Alexus1024Alexus1024

4375 silver badges7 bronze badges

1

if you are using laravel migrations, you can do this using facades helpers

prefer to use Eloquent objects, answer the «Eloquent» way

 Schema::disableForeignKeyConstraints();
 Teacher::truncate();
 Schema::enableForeignKeyConstraints();

In Laravel 7 and 8, for compatibility across 4 databases (MySql, Postgres, SQLite and SqlServer) and no Eloquent, you can use:

Schema::disableForeignKeyConstraints();
    DB::table('teachers')->truncate();
Schema::enableForeignKeyConstraints();

answered Mar 21, 2022 at 10:09

HadiNiazi's user avatar

HadiNiaziHadiNiazi

1,6762 gold badges14 silver badges26 bronze badges

Getting the old foreign key check state and sql mode are best way to truncate / Drop the table as Mysql Workbench do while synchronizing model to database.

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;`
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

DROP TABLE TABLE_NAME;
TRUNCATE TABLE_NAME;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

answered Dec 15, 2017 at 19:02

Vijay Arun's user avatar

Vijay ArunVijay Arun

41410 silver badges15 bronze badges

If the database engine for tables differ you will get this error so change them to InnoDB

ALTER TABLE my_table ENGINE = InnoDB;

answered Aug 20, 2018 at 21:14

sajad abbasi's user avatar

sajad abbasisajad abbasi

1,8802 gold badges22 silver badges40 bronze badges

It’s my solution of this issue. I used it for altering PK, but idea the same. Hope this will be useful)

PRINT 'Script starts'

DECLARE @foreign_key_name varchar(255)
DECLARE @keycnt int
DECLARE @foreign_table varchar(255)
DECLARE @foreign_column_1 varchar(255)
DECLARE @foreign_column_2 varchar(255)
DECLARE @primary_table varchar(255)
DECLARE @primary_column_1 varchar(255)
DECLARE @primary_column_2 varchar(255)
DECLARE @TablN varchar(255)

-->> Type the primary table name
SET @TablN = ''
---------------------------------------------------------------------------------------    ------------------------------
--Here will be created the temporary table with all reference FKs
---------------------------------------------------------------------------------------------------------------------
PRINT 'Creating the temporary table'
select cast(f.name  as varchar(255)) as foreign_key_name
    , r.keycnt
    , cast(c.name as  varchar(255)) as foreign_table
    , cast(fc.name as varchar(255)) as  foreign_column_1
    , cast(fc2.name as varchar(255)) as foreign_column_2
    , cast(p.name as varchar(255)) as primary_table
    , cast(rc.name as varchar(255))  as primary_column_1
    , cast(rc2.name as varchar(255)) as  primary_column_2
    into #ConTab
    from sysobjects f
    inner join sysobjects c on  f.parent_obj = c.id 
    inner join sysreferences r on f.id =  r.constid
    inner join sysobjects p on r.rkeyid = p.id
    inner  join syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid
    inner  join syscolumns fc on r.fkeyid = fc.id and r.fkey1 = fc.colid
    left join  syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc.colid
    left join  syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc.colid
    where f.type =  'F' and p.name = @TablN
 ORDER BY cast(p.name as varchar(255))
---------------------------------------------------------------------------------------------------------------------
--Cursor, below, will drop all reference FKs
---------------------------------------------------------------------------------------------------------------------
DECLARE @CURSOR CURSOR
/*Fill in cursor*/

PRINT 'Cursor 1 starting. All refernce FK will be droped'

SET @CURSOR  = CURSOR SCROLL
FOR
select foreign_key_name
    , keycnt
    , foreign_table
    , foreign_column_1
    , foreign_column_2
    , primary_table
    , primary_column_1
    , primary_column_2
    from #ConTab

OPEN @CURSOR

FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table,         @foreign_column_1, @foreign_column_2, 
                        @primary_table, @primary_column_1, @primary_column_2

WHILE @@FETCH_STATUS = 0
BEGIN

    EXEC ('ALTER TABLE ['+@foreign_table+'] DROP CONSTRAINT ['+@foreign_key_name+']')

FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table, @foreign_column_1, @foreign_column_2, 
                         @primary_table, @primary_column_1, @primary_column_2
END
CLOSE @CURSOR
PRINT 'Cursor 1 finished work'
---------------------------------------------------------------------------------------------------------------------
--Here you should provide the chainging script for the primary table
---------------------------------------------------------------------------------------------------------------------

PRINT 'Altering primary table begin'

TRUNCATE TABLE table_name

PRINT 'Altering finished'

---------------------------------------------------------------------------------------------------------------------
--Cursor, below, will add again all reference FKs
--------------------------------------------------------------------------------------------------------------------

PRINT 'Cursor 2 starting. All refernce FK will added'
SET @CURSOR  = CURSOR SCROLL
FOR
select foreign_key_name
    , keycnt
    , foreign_table
    , foreign_column_1
    , foreign_column_2
    , primary_table
    , primary_column_1
    , primary_column_2
    from #ConTab

OPEN @CURSOR

FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table, @foreign_column_1, @foreign_column_2, 
                         @primary_table, @primary_column_1, @primary_column_2

WHILE @@FETCH_STATUS = 0
BEGIN

    EXEC ('ALTER TABLE [' +@foreign_table+ '] WITH NOCHECK ADD  CONSTRAINT [' +@foreign_key_name+ '] FOREIGN KEY(['+@foreign_column_1+'])
        REFERENCES [' +@primary_table+'] (['+@primary_column_1+'])')

    EXEC ('ALTER TABLE [' +@foreign_table+ '] CHECK CONSTRAINT [' +@foreign_key_name+']')

FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table, @foreign_column_1, @foreign_column_2, 
                         @primary_table, @primary_column_1, @primary_column_2
END
CLOSE @CURSOR
PRINT 'Cursor 2 finished work'
---------------------------------------------------------------------------------------------------------------------
PRINT 'Temporary table droping'
drop table #ConTab
PRINT 'Finish'

The two links in your post point to scripts that generate the commands necessary to drop and recreate all your current foreign keys (I haven’t tested them, just glanced through for intent).

However, in both cases the scripts place those commands in a variable, and print them. Neither script (as shown) automatically executes those commands.

This seems wise to me — I would want to look at the scripts generated, try to understand them, and maybe even compare them to the constraints in a CREATE TABLE script generated by SSMS, to make sure they seem to be complete.

The first link (to a DBA.SE question) would let you DELETE the rows in your table, not actually TRUNCATE, so we’ll ignore that one.

In the MSDN link, to actually drop your foreign keys, you’d need to execute this command after populating @strsql with the necessary commands to do the drop:

EXEC sp_executesql @strsql;

If this is your actual problem, then you need to be very careful to test this all very thoroughly, on a test database, that you can easily restore if you need to.

You must capture the necessary commands to recreate your foreign keys before you drop them. If you ran the necessary commands to drop the foreign keys, then tried to put the commands to recreate them in @strsql (which is what the second chunk of code in the MSDN link does) — you won’t get anything, because your foreign keys will already have been dropped.

Make sure to capture the commands you need to recreate the foreign keys before you drop them. Also, make sure you put the «drop» commands in a different variable than the commands to recreate the foreign keys, since you need to have both sets of commands available before you drop the foreign keys.

The proper order of operation would be:

  • Generate the code to recreate the foreign keys (maybe in to a variable @FK_Create
  • Generate the code to drop the foreign keys (into a variable @FK_Drop)
  • EXECUTE sp_executesql @FKDrop
  • TRUNCATE your tables.
  • EXECUTE sp_executesql @FKCreate

Issue type:

[x] question
[x] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[ ] mysql / mariadb
[ ] oracle
[x] postgres
[x] sqlite
[ ] sqljs
[ ] websql

TypeORM version:

[x] latest
[ ] @next
[ ] 0.x.x (or put your version here)

Steps to reproduce or a small repository showing the problem:

I have simple OneToOne relation like this:

@Entity({name: 'user'})
export class User extends BaseEntity {

  @PrimaryGeneratedColumn()
  id: number;

  @Column({length: '50', nullable: true})
  firstName: string;

  @Column({length: '50', nullable: true})
  lastName: string;

}

and:

@Entity({name: 'account'})
export class Account extends BaseEntity {

  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  balance: number;

  @OneToOne(type => User, {})
  @JoinColumn()
  user: User;
}

Delete all users and create new one with following code:

createConnection().then(async (connection) => {
  console.log('Inserting a new user into the database...');
  await connection.getRepository(User).clear(); // remove all users 
  const user = new User();
  user.email = 'admin@admin.com';
  user.role = Role.BORROWER;
  user.password = 'admin';
  user.idnp = '2342332342300';
  await connection.manager.save(user);
  console.log('Saved a new user with id: ' + user.id);

  console.log('Loading users from the database...');
  const users = await connection.manager.find(User);
  console.log('Loaded users: ', users);

  console.log('Here you can setup and run express/koa/any other framework.');

  debug('postgres database connected successfully...');
}).catch((err) => error(err));

Witch produces the following error (error with postgres, with sqlite this error is not pesent.):

_
Inserting a new user into the database…
executing query: TRUNCATE TABLE «user»
query failed: TRUNCATE TABLE «user»
error: { error: cannot truncate a table referenced in a foreign key constraint
at Connection.parseE { … }backendnode_modulespglibconnection.js:113:22)
at Socket.emit (events.js:160:13)
at addChunk (_stream_readable.js:269:12)
at readableAddChunk (_stream_readable.js:256:11)
at Socket.Readable.push (_stream_readable.js:213:10)
at TCP.onread (net.js:602:20)
name: ‘error’,
length: 238,
severity: ‘ERROR’,
code: ‘0A000’,
detail: ‘Table «account» references «user».’,
hint: ‘Truncate table «account» at the same time, or use TRUNCATE … CASCADE.’,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: ‘heap.c’,
line: ‘2983’,
routine: ‘heap_truncate_check_FKs’ }
error: { QueryFailedError: cannot truncate a table referenced in a foreign key constraint
….
backendnode_modulespglibconnection.js:117:12)
at Socket.emit (events.js:160:13)
at addChunk (_stream_readable.js:269:12)
at readableAddChunk (_stream_readable.js:256:11)
at Socket.Readable.push (_stream_readable.js:213:10)
message: ‘cannot truncate a table referenced in a foreign key constraint’,
name: ‘QueryFailedError’,
length: 238,
severity: ‘ERROR’,
code: ‘0A000’,
detail: ‘Table «account» references «user».’,
hint: ‘Truncate table «account» at the same time, or use TRUNCATE … CASCADE.’,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: ‘heap.c’,
line: ‘2983’,
routine: ‘heap_truncate_check_FKs’,
query: ‘TRUNCATE TABLE «user»‘,
parameters: [] }
internal/child_process.js:409
throw errnoException(err, ‘kill’);
^
_

Scenario: 

You need to truncate a table but when you try to execute truncate table tableName. You get below error.

Msg 4712, Level 16, State 1, Line 43
Cannot truncate table ‘SchemaName.TableName’ because it is being referenced by a FOREIGN KEY constraint.

How would you truncate this table?

Solution:

As the table in involved in Foreign Key relationship, you need to drop the foreign key constraint first and then execute the truncate table statement.

Let’s demo this example, I am going to create two table dbo.Customer and dbo.Orders and then create Foreign Key Constraint on one of the column of Dbo.Orders to dbo.Customer table.

USE YourDatabaseName
GO

CREATE TABLE dbo.Customer (
    Customerid INT PRIMARY KEY
    ,FName VARCHAR(100)
    ,LName VARCHAR(100)
    ,SSN VARCHAR(10)
    )

CREATE TABLE dbo.Orders (
    OrderId INT Identity(1, 1)
    ,OrderitemName VARCHAR(50)
    ,OrderItemAmt INT,
    CustomerId int
    )

--Create Foreign Key Constraint 
    Alter table dbo.Orders with Nocheck
    Add Constraint Fk_CustomerId  
    Foreign Key(CustomerId) References dbo.Customer(CustomerId)



Now if I try to truncate dbo.Orders table, it will throw no error. But when I try to truncate dbo.Customer table it will throw error as dbo.Customer is begin references by Foreign Key Constraint.

How to truncate a table which is reference by Foreign Key Constraint in SQL Server

We can drop the constraint for now, truncate the table and then recreate Foreign key constraint.

To find the Foreign Key Constraints on a table, you can use below statement.

SELECT * 
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('dbo.Customer')


How to get Constraint name from system tables in SQL Server


The below script can be used to generate drop Constraint statement for your table


SELECT 
    'ALTER TABLE ' 
    +  OBJECT_SCHEMA_NAME(parent_object_id) 
    +'.[' + OBJECT_NAME(parent_object_id) 
    +'] DROP CONSTRAINT ' 
    + name as DropFKConstraint
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('dbo.Customer'


How to drop Foreign Key Constraint on Table in SQL Server

Take the result for Drop Foreign Key Constraint and execute, After that run your truncate table statement to truncate table. It should complete without any error.

Video Demo : Cannot truncate table because it is being referenced by a FOREIGN KEY constraint

  • Remove From My Forums
  • Question

  • Hi

    when i was trying to truncate the table(truncate table table_name) i got this bellow error

    Error: Cannot truncate table because it is being referenced by a FOREIGN KEY constraint.

    as per the error i had truncated all child tables data before truncated parent table even i got same error 

    Note: but i’m able to delete the parent table data by using Delete from table_name  command

     i didn’t understand what’s going wrong can any one plz suggest me any reason


    Kamal

Answers

  • You cannot truncate a table which has foreign keys.

    You need to either drop the constraint before you can truncate or use delete statement .

    http://msdn.microsoft.com/en-us/library/ms177570.aspx

    Restrictions

    You cannot use TRUNCATE TABLE on tables that:

    • Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)

    • Participate in an indexed view.

    • Are published by using transactional replication or merge replication.

    For tables with one or more of these characteristics, use the DELETE statement instead.


    Regards, Ashwin Menon My Blog — http:\sqllearnings.com

    • Proposed as answer by

      Thursday, November 27, 2014 11:50 AM

    • Marked as answer by
      Donghui Li
      Friday, December 5, 2014 5:40 AM

  • as per the error i had truncated all child tables data before
    truncated parent table even i got same error 

    Hello Kamal,

    That’s a restriction for truncate, you can not truncate a table as long as a FK constraint exists and it doesn’t matter if the other tables are empty; see
    TRUNCATE TABLE (Transact-SQL) => Remarks => Restrictions


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Edited by
      Olaf HelperMVP
      Thursday, November 27, 2014 11:34 AM
    • Proposed as answer by
      Praveen Rayan D’sa
      Thursday, November 27, 2014 11:41 AM
    • Marked as answer by
      Donghui Li
      Friday, December 5, 2014 5:40 AM

    • Marked as answer by
      Donghui Li
      Friday, December 5, 2014 5:42 AM
    • Proposed as answer by
      Donghui Li
      Friday, November 28, 2014 2:48 AM
    • Marked as answer by
      Donghui Li
      Friday, December 5, 2014 5:42 AM

DISCLOSURE:
This article may contain affiliate links and any sales made through such links will reward us a small commission,
at no extra cost for you. Read more about Affiliate Disclosure here.

In MySQL we create tables and build relationship among them. In the process if we need to truncate foreign key constrained table then we can’t do it simply and MySQL will throw an error even we have already removed table having foreign key constraint:

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint…

I’m presenting here 2 ways to truncate foreign key constrained table in MySQL with a simple category, product tables example.

CREATE TABLE category (

   ID    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

   Name  VARCHAR (100) NOT NULL

) ENGINE=InnoDB;

CREATE TABLE product (

   ID           INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

   CatID        INT NOT NULL,

   Name         VARCHAR (100) NOT NULL,

   Description  VARCHAR (500) NOT NULL,

   FOREIGN KEY (CatID) REFERENCES category(ID) ON DELETE CASCADE

) ENGINE=InnoDB;

In the table above we have tables with foreign key constraints and if we wish to truncate category table then we can’t do it by running simple query TRUNCATE category as we cannot truncate a table that has foreign key constraints applied on it.

2 ways to truncate foreign key constrained table 

First one longer but does not risk damage to data integrity:

  1. Remove constraints
  2. Perform TRUNCATE
  3. Delete manually the rows that now have references to ‘nowhere
  4. Create constraints again

Second one is short but can damage your data integrity. It’s a bad practice and use it if you can risk damage to data integrity.

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE category;

SET FOREIGN_KEY_CHECKS = 1;

So here we are temporarily disabling foreign key checks to forcibly truncate foreign key constrained table (category in our case).

В MySQL, как и в других СУБД можно очищать таблицы. Очистка таблицы позволяет удалять данные при этом не затрагивая саму структуру таблицы. В MySQL существует несколько способов очистки таблицы. В частности, можно выделить очистку таблицы при помощи команд DELETE и TRUNCATE.

Обе команды выполняют одну и ту же задачу, но имеют несколько отличий о которых будет рассказано далее в статье. Также будет упомянуто об удалении данных из таблицы при наличии внешних ключей (foreign key). В данной статье будет рассмотрено как очистить таблицу в MySQL различными способами в операционной системе Ubuntu 20.04.

Для очистки таблицы в MySQL существует несколько способов. Далее будут рассмотрены все возможные способы.

1. Удаление данных таблицы с помощью DELETE

Для удаления данных из таблицы можно воспользоваться инструкцией DELETE которая может удалять строки таблицы по заданному условию. Предположим, есть таблица MyGuests в которой есть пользователь John:

gChghB3nCc1nAAAAABJRU5ErkJggg==

При помощи оператора DELETE и заданного условия — в данном случае удаление будет происходит по столбцу firstname который принимает значение имени пользователя будет удалена запись с номером (id) 1 и именем John:

DELETE FROM MyGuests WHERE firstname = 'John';

0IAAAAASUVORK5CYII=

Также оператор DELETE может удалять все строки таблицы сразу. В качестве примера есть таблица с двумя записями:

wPbRaK+HpwjqAAAAABJRU5ErkJggg==

Удалим все строки за один раз не задавая никаких условий. Для этого необходимо выполнить следующий SQL запрос:

DELETE FROM MyGuests;

D0jAi4fJLLPKAAAAAElFTkSuQmCC

На этом очистка таблицы MySQL завершена.

2. Удаление данных таблицы с помощью TRUNCATE

Также для удаления всех строк в таблице существует специальная команда TRUNCATE. Она схожа с DELETE, однако он не позволяет использовать WHERE. Также стоит выделить следующие особенности при очистки таблицы MySQL с помощью оператора TRUNCATE:

  • 1) TRUNCATE не позволяет удалять отдельные строки;
  • 2) DELETE блокирует каждую строку, а TRUNCATE всю таблицу;
  • 3) TRUNCATE нельзя использовать с таблицами содержащими внешние ключи других таблиц;
  • 4) После использования оператора TRUNCATE в консоль не выводится информация о количестве удаленных строк из таблицы.

В качестве примера возьмем таблицу с двумя записями из предыдущего примера:

wPbRaK+HpwjqAAAAABJRU5ErkJggg==

Для очистки этой таблицы MySQL от всех записей необходимо выполнить следующий SQL запрос:

TRUNCATE MyGuests;

AAAAAElFTkSuQmCC

Как уже было упомянуто ранее команда TRUNCTE не выводит количество удалённых строк в таблице поэтому в консоль был выведен текст 0 rows affected.

Как очистить таблицу с Foreign Key Constraint

Если в таблице присутствуют внешние ключи (Foreign Key) то просто так очистить таблицу не получится. Предположим, есть 2 таблицы — Equipment и EquipmentCategory:

uAAAAABJRU5ErkJggg==

В таблице Equipment присутствует столбец с именем category_id, который связан внешним ключом со столбцом id в другой таблице — EquipmentCategory. Например:

Equipment:

  • id
  • category_id
  • name

EquipmentCategory:

  • id
  • name

Если попытаться очистить все строки таблицы EquipmentCategory при помощи оператора TRUNCATE то будет выведена следующая ошибка:

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`Inventory`.`Equipment`, CONSTRAINT `Equipment_ibfk_1`)

H4MLkDZh0EBYAAAAAElFTkSuQmCC

Данная ошибка говорит о том, что таблица ссылается на другую таблицу и имеет ограничение на удаление в виде внешнего ключа. Чтобы избежать данной ошибки можно воспользоваться отключением проверки внешних ключей и добавлением параметра ON DELETE CASCADE при создании таблицы.

1. Отключение проверки внешних ключей

Чтобы очистить таблицу при наличии в ней внешних ключей можно отключить проверку внешних ключей. Для этого нужно выполнить следующую команду:

SET FOREIGN_KEY_CHECKS=0;

cDloNFGT405U9o5GdyKv3wcICGE9BUwIwEz9hXWJApv5jiQbBW8OD89ghZ5dKHAQxqNL1ASiyXUDDP2HV3yU+WQrB5gTNuSaQmCzcLJsVMgq8T6SqcJKcmJvGuK7byVmWpnhifH36Gbao9GAIKASBrpwASvOGqVaXrMaJBfkWm0qmJYWvuIsgUJDH5n6WeLs+MHx7cDpeql3Im0lSrzTXJU575f5TWzoezznzLAAAAAElFTkSuQmCC

Данная команда отключит проверку внешних ключей и тем самым позволит очистить таблицу при помощи команды TRUNCATE:

TRUNCATE Equipment;

AAAAAElFTkSuQmCC

Также очистить таблицу можно и при помощи DELETE:

DELETE FROM Equipment;

D0jAi4fJLLPKAAAAAElFTkSuQmCC

После очистки таблицы можно вернуть проверку внешних ключей при помощи команды:

SET FOREIGN_KEY_CHECKS=1;

GRa4gAAAABJRU5ErkJggg==

Однако такой способ использовать не рекомендуется, потому что вы теряете консистентность данных в базе данных и программа использующая её может работать некорректно. Есть и другое решение. При удалении таблицы удалять все связанные с ней записи в других таблицах.

2. Добавление опции ON DELETE CASCADE

Опция ON DELETE CASCADE используется для неявного удаления строк из дочерней таблицы всякий раз, когда строки удаляются из родительской таблицы. Опция ON DELETE CASCADE её можно задать при создании таблицы, однако если вы этого не сделали, то можно удалить CONSTRAINT и создать его заново.

Для просмотра информации о внешнем ключе содержащемся в таблице необходимо выполнить команду SHOW CREATE TABLE:

SHOW CREATE TABLE EquipmentCategory;

В данном примере таблица называется EquipmentCategory и в ней присутствует константа с именем EquipmentCategory_ibfk_1.

Далее необходимо удалить внешний ключ при помощи команд ALTER TABLE и DROP. Например, если EquipmentCategoryимя таблицы, а EquipmentCategory_ibfk_1имя внешнего ключа выполните:

ALTER TABLE EquipmentCategory DROP FOREIGN KEY EquipmentCategory_ibfk_1;

После этого внешний ключ необходимо вернуть обратно добавив к нему опцию ON DELETE CASCADE. Команда будет следующей:

ALTER TABLE EquipmentCategory ADD FOREIGN KEY (category_id) references EquipmentCategory(id) on DELETE CASCADE;

После этого таблицу можно очистить при помощи команды TRUNCATE:

TRUNCATE EquipmentCategory;

Выводы

В данной статье было рассмотрено как очистить таблицу MySQL от данных. Были рассмотрены команды DELETE и TRUNCATE а также созданы таблицы с наполненной информацией для демонстрации удаления информации. Если у вас остались вопросы задавайте их в комментариях!

Creative Commons License

Статья распространяется под лицензией Creative Commons ShareAlike 4.0 при копировании материала ссылка на источник обязательна .

In this article we will discuss on when we get the error like below in Sql Server and how to resolve it.

Msg 4712, Level 16, State 1, Line 1
Cannot truncate table ‘Customer’ because it is being referenced by a FOREIGN KEY constraint.

To demonstrate this error let us first create a demo db ‘SqlhintsTruncateDemo’, two tables Customer and Order.

CREATE DATABASE SqlhintsTruncateDemo
GO
USE SqlhintsTruncateDemo
GO
CREATE TABLE [dbo].[Customer] (
	[CustID] [int] IDENTITY (1, 1) NOT NULL ,
	CONSTRAINT [PK_Customer] PRIMARY KEY  CLUSTERED 
	(
		[CustID]
	) 
) 
GO
CREATE TABLE [dbo].[Order] (
	[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
	[CustID] [int] NOT NULL ,
	CONSTRAINT [PK_Order] PRIMARY KEY  CLUSTERED 
	(
		[OrderID]
	),
	CONSTRAINT [FK_Order_Customer] FOREIGN KEY 
	(
		[CustID]
	) REFERENCES [dbo].[Customer] (
		[CustID]
	) 
) 
GO

[ALSO READ] Truncate all/all except few/specified Tables of a Database in Sql Server

In the above script we have created a foreign key constraint FK_Order_Customer on the CustID column of the Order table which is referring to the CustID primary key column of the Customer table.

Disclaimer: As TRUNCATE table removes all the records from the table. Be careful before issuing this command.

Now try to truncate the Order Table

TRUNCATE TABLE [dbo].[Order]

RESULT:
Truncate Table Successful

As per the above result truncation of the Order table is successful. Now try to truncate the Customer Table

TRUNCATE TABLE [dbo].[Customer]

RESULT:
Truncate Table Failure

As per the result the truncation of the Customer table is failing, because the
CustID column of the Customer table is referenced by the CustID column of the Order Table.

If we still want to Truncate the table, then we have to drop all the foreign key
constraints which are referring to the table to be truncated.

So now drop the foreign key constraint FK_Order_Customer and then try truncating
the Customer table.

ALTER TABLE [dbo].[Order]
DROP CONSTRAINT FK_Order_Customer
GO
TRUNCATE TABLE [dbo].[Customer]
GO

RESULT:
Truncate Table Successful After Dropping of Foreign Key Constraint

As per the above result it is clear that now the table truncation is successful after dropping all the foreign key constraints which are refering to the table to be truncated.

We can use script like below to identify whether a Table is referenced by another Tables foreign key constraints in Sql Server.

SELECT OBJECT_NAME (FK.referenced_object_id) 'Referenced Table',
OBJECT_NAME(FK.parent_object_id) 'Referring Table',
FK.name 'Foreign Key',
COL_NAME(FK.referenced_object_id, FKC.referenced_column_id) 'Referenced Column',
COL_NAME(FK.parent_object_id,FKC.parent_column_id) 'Referring Column'
FROM sys.foreign_keys AS FK
INNER JOIN sys.foreign_key_columns AS FKC
ON FKC.constraint_object_id = FK.OBJECT_ID
WHERE OBJECT_NAME (FK.referenced_object_id) = 'Enter Table Name'

In the above script replace the string ‘Enter Table Name’ with the table name for which you want to find out the referencing tables and the refering foreign key constraint name.

[ALSO READ] Truncate all/all except few/specified Tables of a Database in Sql Server

Понравилась статья? Поделить с друзьями:
  • Error cannot stat filename too long
  • Error cannot start process the working directory is not a directory
  • Error cannot start process the working directory does not exist
  • Error cannot start a hubconnection that is not in the disconnected state
  • Error cannot squash without a previous commit