DAT BLOG POST 9

Session 15 – Sub queries, referential integrity and cascades

What Have I Learned?

This week I learned about Sub queries, referential integrity, and cascades.

Sub queries: As stated by MySQLTutorial (2020), “A MySQL subquery is a query nested within another query such as SELECT, INSERT, UPDATE or DELETE. In addition, a subquery can be nested inside another subquery.” The query itself is referred to as an inner query whereas the statement that encapsulates it (i.e. SELECT) is referred to as the outer query.

Below we can see an example of how the query might be formatted:

Referential integrity:

In database architecture, referential integrity is a crucial notion. The phrase refers to a condition in which all database references are legitimate, and no incorrect connections exist between the system's various tables. Any attempt to connect to a record that does not exist will fail if referential integrity is present. This helps to reduce user mistakes, resulting in a more accurate/reliable and usable database (Melonfire, 2006).

We generally implement referential integrity with the use of foreign keys. As stated by w3schools (n.d.), “The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables. A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.”

Below we can see an example of the use of constraints within a database:

Cascades:

Cascades are a method in which we can delete all relating records of child tables related to that of a deleted parent table. This relates back to the previous part on referential integrity. When we delete a record such as a user account, it is likely that we want to remove any other information about that user. If this information is spread across multiple tables, this data is not just removed when the parent is deleted. Therefor, we use on delete cascades to remove all child records.

Below we can see an example of the use of CASCADE within a database:

---

Why Have I Learned This?

I believe that I have learned this as it is a useful technique to ensure that data is not left in the database that is relevant to records that we have deleted. We do not want to use space with redundant data that could be filled with useful data.

---

How Have I Learned This?

I have learned this by doing some research into what sub queries, referential integrity, and cascades are, and how I implement them into my code.

Melonfire, C. (2006, February 6). An introduction to foreign keys and referential integrity in MySQL. TechRepublic. https://www.techrepublic.com/article/an-introduction-to-foreign-keys-and-referential-integrity-in-mysql/

MySQLTutorial. (2020, April 11). MySQL Subquery. https://www.mysqltutorial.org/mysql-subquery/