Система управления базами данных SQLite. Изучаем язык запросов SQL и реляционные базы данных на примере библиотекой SQLite3. Курс для начинающих.
Часть 11.6: Каскадное удаления данных в базах данных SQLite
- 27.06.2016
- SQLite библиотека, Базы данных
- 5 комментариев
Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. Прошлая публикация была про каскадную модификацию, в этой мы с вами поговорим про каскадное удаление данных из базы данных SQLite3. Каскадное удаление данных это способ удалить данные из связанных таблиц и при этом не нарушить ограничение внешнего ключа, кроме того, каскадное удаление данных упрощает нам работу при удалении строк в связанных таблицах.
Каскадное удаления данных в базах данных SQLite
Из этой записи вы узнаете для чего нужно каскадное удаление данных в реляционных базах данных и, как каскадное удаление облегчает жизнь разработчику. И увидите пример работы каскадного удаления данных из базы данных под управлением SQLite в таблицах, связанных связью многие ко многим, в общем, увидите в действие конструкцию ON DELETE CASCADE.
Каскадное удаление данных из базы данных SQLite
Мы уже рассмотрели всевозможные способы обеспечения целостности данных в базах данных под управлением SQLite. Успели разобраться с ограничениями уровня столбца и ограничениями уровня таблицы и чуть ранее рассмотрели каскадное обновление данных. Теперь перейдем к каскадному удалению данных в базах данных SQLite3.
При реализации связей между таблицами при помощи внешнего ключа или FOREIGN KEY мы сталкиваемся с такой проблемой: для удаления данных из таблиц базы данных нам необходимо выполнить две команды DELETE: сперва нужно удалить данные из таблицы справочника, а затем удалить строку из таблицы, которая ссылается на справочник. В противном случае SQLite скажет, что было нарушено ограничение внешнего ключа и не даст удалить данные.
Чтобы избежать такой проблемы и не писать лишние SQL запросы в SQLite было реализовано каскадное удаление данных из таблиц, при каскадном удаление данных мы не пишем лишний SQL запрос DELETE, а также гарантируем себе то, что целостность данных в базах данных не будет нарушена.
Операция каскадного удаления данных реализуется при помощи ключевой фразы ON DELETE CASCADE. Фраза ON DELETE CASCADE говорит SQLite о том, что сперва нужно удалить данные из справочника или домена данных, а затем данные нужно удалять из таблицы, которая ссылается на этот справочник.
Чтобы реализовать каскадное удаление данных из таблицы базы данных, нужно записать правило ON DELETE сразу после FOREIGN KEY, тогда SQLite начнет «понимать», что при удалении данных из справочника, ей нужно удалять и данные из связанных таблиц.
Давайте реализуем пример связи многие ко многим и посмотрим, как происходит каскадное удаление данных из базы данных SQLite3 и поймем, как работает конструкция ON DELETE CASCADE.
Примеры каскадного удаления данных из базе данных SQLite. ON DELETE в SQLite3
Чтобы посмотреть, как удалить данные каскадом, давайте создадим три таблицы в базе данных, которые будут связаны связью многие ко многим, воспользуемся командой CREATE:
MySQL ON DELETE CASCADE
Summary: in this tutorial, you will learn how to use MySQL ON DELETE CASCADE referential action for a foreign key to delete data from multiple related tables.
In the previous tutorial, you learned how to delete data from multiple related tables using a single DELETE statement. However, MySQL provides a more effective way called ON DELETE CASCADE referential action for a foreign key that allows you to delete data from child tables automatically when you delete the data from the parent table.
MySQL ON DELETE CASCADE example
Let’s take a look at an example of using MySQL ON DELETE CASCADE .
Suppose that we have two tables: buildings and rooms . In this database model, each building has one or many rooms. However, each room belongs to one only one building. A room would not exist without a building.
The relationship between the buildings and rooms tables is one-to-many (1:N) as illustrated in the following database diagram:
When you delete a row from the buildings table, you also want to delete all rows in the rooms table that references to the row in the buildings table. For example, when you delete a row with building no. 2 in the buildings table as the following query:
You also want the rows in the rooms table that refers to building number 2 will be also removed.
The following are steps that demonstrate how the ON DELETE CASCADE referential action works.
Step 1. Create the buildings table:
Step 2. Create the rooms table:
Notice that the ON DELETE CASCADE clause at the end of the foreign key constraint definition.
Step 3. Insert rows into the buildings table:
Step 4. Query data from the buildings table:
We have two rows in the buildings table.
Step 5. Insert rows into the rooms table:
Step 6. Query data from the rooms table:
We have three rooms that belong to building no 1 and two rooms that belong to the building no 2.
Step 7. Delete the building with building no. 2:
Step 8. Query data from rooms table:
As you can see, all the rows that reference to building_no 2 were automatically deleted.
Notice that ON DELETE CASCADE works only with tables with the storage engines that support foreign keys e.g., InnoDB.
Some table types do not support foreign keys such as MyISAM so you should choose appropriate storage engines for the tables that you plan to use the MySQL ON DELETE CASCADE referential action.
Tips to find tables affected by MySQL ON DELETE CASCADE action
Sometimes, it is useful to know which table is affected by the ON DELETE CASCADE referential action when you delete data from a table. You can query this data from the referential_constraints in the information_schema database as follows:
For example, to find tables that associated with the buildings table with the CASCADE deletion rule in the classicmodels database, you use the following query:
In this tutorial, you have learned how to use the MySQL ON DELETE CASCADE referential action for a foreign key to delete data automatically from the child tables when you delete data from the parent table.
Cascade in SQL
CASCADE in SQL is used to simultaneously delete or update an entry from both the child and parent table. The keyword CASCADE is used as a conjunction while writing the query of ON DELETE or ON UPDATE . If the cascade keyword is added to a query written for the parent table, then both the parent and child tables change accordingly on the execution of the query.
Scope of the Article
- In this article, we will learn the use of the cascade keyword while writing SQL queries.
- Use of cascade while using the ON DELETE query.
- Use of cascade while using the ON UPDATE query.
Introduction
CASCADE is a keyword in MySQL relational database management system. Let us take an example in layman's terms to understand the usage of cascade keyword in SQL.
Consider a table named T1 which contains the data of all the students in a class. Table T1 has four columns, i.e., Enrollment number, name, email, and state of the student. Let us now consider two more tables, i.e., T2 and T3. Table T2 contains the marks of all the students in the Mathematics subject. Table T3 contains the marks of all the students in the Science subject. Both tables, T2 and T3, have three columns, i.e., enrollment number, name of the students, and marks of the student. Now, if there is a case where a student wants to change a class or change his name/enrollment number, we need to make certain changes in the database.
Following is the design of our MySQL database (arrangement of tables).
Case 1: If a student wants to change his class, he must delete his name from all the tables, i.e., T1, T2, and T3. As the student will be shifted to another class, so his information needs to be deleted.
Case 2: If a student wants to change his name/enrollment number, he has to update all three tables, i.e., T1, T2, and T3.
If we try to perform the scenarios provided in the above cases in a traditional fashion, the changes would need to be done manually in all three tables. We use the keyword CASCADE to overcome this problem in SQL queries. With a single command, we can change all three tables simultaneously.
Using a similar example, we will learn how to use CASCADE in SQL while deleting/updating an entry in the parent and child table. To do so, we will define our database more efficiently.
Setting up Database
As we have three tables, T1, T2, and T3, each table should be in the following format.
Table T1
Primary key: Enrollment number (int); name (varchar(20)), email-id (varchar(20)), and State of the student (varchar(20)) are regular columns.
Tables T2 & T3
Primary key: Enrollment number (int), name (varchar(20)); Marks of the student (int) is a regular column.
Creating Table T1
To see all the columns created in Table T1, run the following command.
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| Enrollment | Int | NO | PRI | NULL | |
| Student_Name | varchar(20) | YES | NULL | ||
| Email_id | varchar(20) | YES | NULL | ||
| State | varchar(20) | YES | NULL |
Creating Table T2 and T3.
As we can see, while creating tables T2 and T3, we added the keywords ON DETELE CASCADE and ON UPDATE CASCADE on the line where the FOREIGN KEY is declared.
Lets us check all the columns from table T2 and T3.
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| Enrollment | int | NO | PRI | NULL | |
| Student_Name | varchar(20) | NO | PRI | NULL | |
| Marks | int | YES | NULL |
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| Enrollment | int | NO | PRI | NULL | |
| Student_Name | varchar(20) | NO | PRI | NULL | |
| Marks | int | YES | NULL |
Let us insert some data into the table T1 (Students in the class).
Use the following query to view all the data of the Class table.
| Enrollment | Student_Name | Email_id | State |
|---|---|---|---|
| 1 | Rishab P | rishab@gmail.com | WB |
| 2 | Mayank G | mayank@gmail.com | JH |
| 3 | Rajat A | rajat@gmail.com | UP |
| 4 | Debojeet J | debojeet@gmail.com | WB |
| 5 | Soham M | soham@gmail.com | MH |
Now, insert data into tables T2 and T3.
Inserting values into the Science table.
Printing all the data from the Science table.
| Enrollment | Student_Name | Marks |
|---|---|---|
| 1 | Rishab P | 85 |
| 3 | Rajat A | 87 |
| 5 | Soham M | 95 |
Inserting values into the Mathematics table.
Printing all the data from the Mathematics table.
| Enrollment | Student_Name | Marks |
|---|---|---|
| 1 | Rishab P | 90 |
| 2 | Mayank G | 86 |
| 4 | Debojeet J | 83 |
We have created tables T1, T2, and T3 and added the data to the tables.
ON DELETE CASCADE in SQL
In this section, we will discuss the ON DELETE CASCADE method to solve Case 1, which we have seen in the introduction. If the student Rishab P changes his Class, then his name must be removed from all the tables, i.e., T1, T2, and T3.
Deleting an entry of a particular student from all three tables needs three queries to be executed, but while setting up the database of tables T2 and T3, we have used an ON DELETE CASCADE keyword. Because of the CASCADE keyword, we can delete the entry from the Parent Table T1, and it will automatically delete the name of the student from the table T2 and T3.
Example of ON DELETE CASCADE in SQL
Deleting the entry of student Rishab P from the Class table.
As we have only deleted the entry from the Class table, it will automatically reflect the changes in the other two tables also.
Displaying the data of the Class table.
| Enrollment | Student_Name | Email_id | State |
|---|---|---|---|
| 2 | Mayank G | mayank@gmail.com | JH |
| 3 | Rajat A | rajat@gmail.com | UP |
| 4 | Debojeet J | debojeet@gmail.com | WB |
| 5 | Soham M | soham@gmail.com | MH |
Displaying the data of Mathematics table.
| Enrollment | Student_Name | Marks |
|---|---|---|
| 2 | Mayank G | 86 |
| 4 | Debojeet J | 83 |
Displaying the data of Science table.
| Enrollment | Student_Name | Marks |
|---|---|---|
| 3 | Rajat A | 87 |
| 5 | Soham M | 95 |
As we can see from the above output, the entry of the student named Rishab P has been removed from all three tables.
ON UPDATE CASCADE in SQL
The student with enrollment number 1 was deleted when we executed the query in the previous section. So, we will now update the enrollment number of the last student in the table to the value 1. The last student has an enrollment number equal to 5 currently. The entry for the student with enrollment number 5 is present in two tables, i.e., the Class and Science table.
To update both tables traditionally, it will take two SQL queries. But, as we have added a keyword, ON UPDATE CASCADE while setting up the parent table's database, the Science table entry will automatically get updated.
Example of ON UPDATE CASCADE in SQL
Updating the enrollment number of the student having enrollment 5 (updating it to 1).
We have only updated the entry from the Class table, but we can verify that it will also automatically reflect the changes in the other tables.
Displaying the data of the Class table.
| Enrollment | Student_Name | Email_id | State |
|---|---|---|---|
| 1 | Soham M | soham@gmail.com | MH |
| 2 | Mayank G | mayank@gmail.com | JH |
| 3 | Rajat A | rajat@gmail.com | UP |
| 4 | Debojeet J | debojeet@gmail.com | WB |
Displaying the data of Science table.
| Enrollment | Student_Name | Marks |
|---|---|---|
| 1 | Soham M | 95 |
| 3 | Rajat A | 87 |
As we can see in the above table that the student having the enrollment 5 is changed it enrollment to 1.
ON INSERT CASCADE in SQL
Many people find this title amusing since it refers to a fictitious feature that people wrongly mention when discussing cascade constraints. The main idea of using ON INSERT CASCADE is that whenever a data entry is made in the parent table, and we also want to make the same entry in the child table, we use this cascade. This part will take a different example to understand ON INSERT CASCADE.
Example of ON INSERT CASCADE in SQL
First, we will create two tables, i.e., item and stock. The first table item will be the parent table, and the second table stock will be the child table. Whenever we make an entry in the item table, the stock table gets updated automatically by entering new data.
Query to create Item Table
Query to create Stocks Table
Here, the item table has three columns, i.e., Id (Primary Key), Name, and the product's price. The stock table has two columns, i.e., Id(Primary Key, Foreign Key reference from Id of item table), and Quantity.
Now, whenever a new product is launched, we need to insert the product name into the item table and set that item's quantity to zero in the stocks table. Manually this can be done by executing two queries, but using the ON INSERT CASCADE technique, this can be done within a single query.
To do so, we need to use TRIGGERS.
Implementing ON INSERT CASCADE
The above query will automatically insert the data into the stocks table.
How do I use cascade delete with SQL Server?
I have 2 tables: T1 and T2, they are existing tables with data. We have a one to many relationship between T1 and T2. How do I alter the table definitions to perform cascading delete in SQL Server when a record from T1 is deleted, all associated records in T2 also deleted.
The foreign constraint is in place between them. I don’t want to drop the tables or create a trigger to do the deletion for T2. For example, when I delete an employee, all the review record should be gone, too.
T2 — Performance Reviews,
8 Answers 8
To add «Cascade delete» to an existing foreign key in SQL Server Management Studio:
First, select your Foreign Key, and open it’s «DROP and Create To..» in a new Query window.
Then, just add ON DELETE CASCADE to the ADD CONSTRAINT command:
And hit the «Execute» button to run this query.
By the way, to get a list of your Foreign Keys, and see which ones have «Cascade delete» turned on, you can run this script:
And if you ever find that you can’t DROP a particular table due to a Foreign Key constraint, but you can’t work out which FK is causing the problem, then you can run this command:
The SQL in that article lists all FKs which reference a particular table.