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
, CONSTRAINTinstance_ibfk_1
FOREIGN KEY (GroupID
) REFERENCESmytest
.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
9,5318 gold badges64 silver badges102 bronze badges
asked Mar 27, 2011 at 21:57
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
9,1859 gold badges45 silver badges63 bronze badges
answered Nov 10, 2011 at 3:02
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:
- Remove constraints
- Perform
TRUNCATE
- Delete manually the rows that now have references to nowhere
- 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
8,3253 gold badges48 silver badges70 bronze badges
answered Mar 27, 2011 at 22:03
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
9,1859 gold badges45 silver badges63 bronze badges
answered Sep 24, 2014 at 11:05
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>
answered Nov 5, 2018 at 12:29
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
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 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
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 headingDelete 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 theYes
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
2,72013 gold badges26 silver badges42 bronze badges
answered Dec 22, 2016 at 11:41
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:
- Remove constraints
- Perform TRUNCATE
- Delete manually the rows that now have references to nowhere
- 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:
-
Run
SHOW CREATE TABLE <Table Name>
query to see what is your FOREIGN KEY‘s name (Red frame in below image): -
Run
ALTER TABLE <Table Name> DROP FOREIGN KEY <Foreign Key Name>
. This will remove the foreign key constraint. -
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
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
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.
- Login to PHPMYADMIN and click the table you want to truncate.
- 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. - At the bottom of the editor untick the «Enable foreign key checks» checkbox as shown below:
It will work like magic.
answered Jan 18, 2022 at 18:02
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
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
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 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 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 errorHello 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
-
Edited by
-
-
Marked as answer by
Donghui Li
Friday, December 5, 2014 5:42 AM
-
Marked as answer by
-
-
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
-
Proposed as answer by
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:
- Remove constraints
- Perform TRUNCATE
- Delete manually the rows that now have references to ‘nowhere‘
- 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:
При помощи оператора DELETE и заданного условия — в данном случае удаление будет происходит по столбцу firstname который принимает значение имени пользователя будет удалена запись с номером (id) 1 и именем John:
DELETE FROM MyGuests WHERE firstname = 'John';
Также оператор DELETE может удалять все строки таблицы сразу. В качестве примера есть таблица с двумя записями:
Удалим все строки за один раз не задавая никаких условий. Для этого необходимо выполнить следующий SQL запрос:
DELETE FROM MyGuests;
На этом очистка таблицы MySQL завершена.
2. Удаление данных таблицы с помощью TRUNCATE
Также для удаления всех строк в таблице существует специальная команда TRUNCATE. Она схожа с DELETE, однако он не позволяет использовать WHERE. Также стоит выделить следующие особенности при очистки таблицы MySQL с помощью оператора TRUNCATE:
- 1) TRUNCATE не позволяет удалять отдельные строки;
- 2) DELETE блокирует каждую строку, а TRUNCATE всю таблицу;
- 3) TRUNCATE нельзя использовать с таблицами содержащими внешние ключи других таблиц;
- 4) После использования оператора TRUNCATE в консоль не выводится информация о количестве удаленных строк из таблицы.
В качестве примера возьмем таблицу с двумя записями из предыдущего примера:
Для очистки этой таблицы MySQL от всех записей необходимо выполнить следующий SQL запрос:
TRUNCATE MyGuests;
Как уже было упомянуто ранее команда TRUNCTE не выводит количество удалённых строк в таблице поэтому в консоль был выведен текст 0 rows affected.
Как очистить таблицу с Foreign Key Constraint
Если в таблице присутствуют внешние ключи (Foreign Key) то просто так очистить таблицу не получится. Предположим, есть 2 таблицы — Equipment и EquipmentCategory:
В таблице 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`)
Данная ошибка говорит о том, что таблица ссылается на другую таблицу и имеет ограничение на удаление в виде внешнего ключа. Чтобы избежать данной ошибки можно воспользоваться отключением проверки внешних ключей и добавлением параметра ON DELETE CASCADE при создании таблицы.
1. Отключение проверки внешних ключей
Чтобы очистить таблицу при наличии в ней внешних ключей можно отключить проверку внешних ключей. Для этого нужно выполнить следующую команду:
SET FOREIGN_KEY_CHECKS=0;
Данная команда отключит проверку внешних ключей и тем самым позволит очистить таблицу при помощи команды TRUNCATE:
TRUNCATE Equipment;
Также очистить таблицу можно и при помощи DELETE:
DELETE FROM Equipment;
После очистки таблицы можно вернуть проверку внешних ключей при помощи команды:
SET FOREIGN_KEY_CHECKS=1;
Однако такой способ использовать не рекомендуется, потому что вы теряете консистентность данных в базе данных и программа использующая её может работать некорректно. Есть и другое решение. При удалении таблицы удалять все связанные с ней записи в других таблицах.
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 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:
As per the above result truncation of the Order table is successful. Now try to truncate the Customer Table
TRUNCATE TABLE [dbo].[Customer]
RESULT:
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:
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