Section 09 Modifying Data in MySQL/41 MySQL ON DELETE CASCADE.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "9593267d-1789-4b14-a743-18dfb9fec121",
   "metadata": {
    "tags": [],
    "toc-hr-collapsed": true
   },
   "source": [
    "## **[MySQL ON DELETE CASCADE](https://www.mysqltutorial.org/mysql-on-delete-cascade/)**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e6620b2-cf1a-465e-8bdb-cd164ec01a5e",
   "metadata": {},
   "source": [
    "Delete data from child tables automatically when you delete the data from the parent table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2f0f98f9-3a4a-4ade-9fff-5cefea1e7576",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.image as mpimg\n",
    "from IPython.display import Image, SVG\n",
    "\n",
    "from sqlalchemy_utils import database_exists, create_database\n",
    "from sqlalchemy import create_engine, inspect, MetaData, text\n",
    "from sqlalchemy_schemadisplay import create_schema_graph\n",
    "import pymysql\n",
    "\n",
    "pd.set_option(\n",
    "    'display.max_columns', None,\n",
    "    'expand_frame_repr', True,\n",
    "    'display.max_colwidth', None,\n",
    "    'display.max_rows', 10,\n",
    ")\n",
    "\n",
    "pd.set_option('display.width', 65)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1111daf5-4b8d-45c5-9452-f32b0bc1aef6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# connect to the classicmodels database\n",
    "connect_args={'ssl':{'fake_flag_to_enable_tls': True}}\n",
    "\n",
    "engine = create_engine('mysql+pymysql://namlq:abc123@localhost/classicmodels',\n",
    "                       connect_args=connect_args, echo=False\n",
    "                         )\n",
    "inspector = inspect(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c5e9fb7f-148c-41cf-8cf2-39a13da137a0",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL ON DELETE CASCADE example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b403c787-d759-46c0-8f09-2dd17793cf5f",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_7756/3111580915.py:2: RemovedIn20Warning: Deprecated API features detected! These feature(s) are not compatible with SQLAlchemy 2.0. To prevent incompatible upgrades prior to updating applications, ensure requirements files are pinned to \"sqlalchemy<2.0\". Set environment variable SQLALCHEMY_WARN_20=1 to show all deprecation warnings.  Set environment variable SQLALCHEMY_SILENCE_UBER_WARNING=1 to silence this message. (Background on SQLAlchemy 2.0 at: https://sqlalche.me/e/b8d9)\n",
      "  engine.execute('DROP TABLE IF EXISTS buildings, rooms')\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7f23b0111e50>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# delete the existed table (if any):\n",
    "engine.execute('DROP TABLE IF EXISTS buildings, rooms')\n",
    "\n",
    "# create the buildings table:\n",
    "string = '''\n",
    "CREATE TABLE buildings (\n",
    "    building_no INT PRIMARY KEY AUTO_INCREMENT,\n",
    "    building_name VARCHAR(255) NOT NULL,\n",
    "    address VARCHAR(255) NOT NULL)\n",
    ";'''\n",
    "\n",
    "engine.execute(string)\n",
    "\n",
    "# create the rooms table:\n",
    "string = '''\n",
    "CREATE TABLE rooms (\n",
    "    room_no INT PRIMARY KEY AUTO_INCREMENT,\n",
    "    room_name VARCHAR(255) NOT NULL,\n",
    "    building_no INT NOT NULL,\n",
    "    FOREIGN KEY (building_no) \n",
    "        REFERENCES buildings (building_no)\n",
    "        ON DELETE CASCADE\n",
    "); '''\n",
    "\n",
    "engine.execute(string)\n",
    "\n",
    "# insert rows into the buildings table:\n",
    "string = '''\n",
    "INSERT INTO buildings(building_name, address)\n",
    "VALUES ('ACME Headquaters', '3950 North 1st Street CA 95134'),\n",
    "       ('ACME Sales', '5000 North 1st Street CA 95134')\n",
    ";'''\n",
    "\n",
    "engine.execute(string)\n",
    "\n",
    "# insert rows into the rooms table:\n",
    "string = '''\n",
    "INSERT INTO rooms(room_name, building_no)\n",
    "VALUES ('Amazon', 1), ('War Room', 1), ('Office of CEO', 1),\n",
    "       ('Marketing', 2), ('Showroom', 2)\n",
    ";'''\n",
    "\n",
    "engine.execute(string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "f51da4d6-940e-4d00-a086-0bb891e87e18",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>building_no</th>\n",
       "      <th>building_name</th>\n",
       "      <th>address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>ACME Headquaters</td>\n",
       "      <td>3950 North 1st Street CA 95134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>ACME Sales</td>\n",
       "      <td>5000 North 1st Street CA 95134</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   building_no     building_name  \\\n",
       "0            1  ACME Headquaters   \n",
       "1            2        ACME Sales   \n",
       "\n",
       "                          address  \n",
       "0  3950 North 1st Street CA 95134  \n",
       "1  5000 North 1st Street CA 95134  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# query the data from the buildings table:\n",
    "buildings = pd.read_sql('SELECT * FROM buildings', engine)\n",
    "buildings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "aedc77ca-cbdc-48b3-8fbd-72c6b36879cd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>room_no</th>\n",
       "      <th>room_name</th>\n",
       "      <th>building_no</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>War Room</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Office of CEO</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Marketing</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Showroom</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   room_no      room_name  building_no\n",
       "0        1         Amazon            1\n",
       "1        2       War Room            1\n",
       "2        3  Office of CEO            1\n",
       "3        4      Marketing            2\n",
       "4        5       Showroom            2"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# query the data from the rooms table:\n",
    "rooms = pd.read_sql('SELECT * FROM rooms', engine)\n",
    "rooms"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "c3169848-820d-4523-891f-38be21969cd4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7f233905ffd0>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Delete the building with building no. 2:\n",
    "string = '''\n",
    "DELETE FROM buildings\n",
    "WHERE building_no = 2\n",
    ";'''\n",
    "\n",
    "engine.execute(string)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "9f04b857-8d5f-4350-8529-8790fbd3f663",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>building_no</th>\n",
       "      <th>building_name</th>\n",
       "      <th>address</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>ACME Headquaters</td>\n",
       "      <td>3950 North 1st Street CA 95134</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   building_no     building_name  \\\n",
       "0            1  ACME Headquaters   \n",
       "\n",
       "                          address  \n",
       "0  3950 North 1st Street CA 95134  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# query data from buildings table:\n",
    "pd.read_sql('SELECT * FROM buildings', engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "e12c42b9-f216-490e-bf5a-a6ca0272f027",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>room_no</th>\n",
       "      <th>room_name</th>\n",
       "      <th>building_no</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>War Room</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Office of CEO</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   room_no      room_name  building_no\n",
       "0        1         Amazon            1\n",
       "1        2       War Room            1\n",
       "2        3  Office of CEO            1"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# query data from rooms table:\n",
    "pd.read_sql('SELECT * FROM rooms', engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "145813bb-5a4d-4d83-b994-90a2260e1a32",
   "metadata": {},
   "source": [
    "Pandas codes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "85db4435-c8ad-481f-baf0-e6931fb3f120",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>room_no</th>\n",
       "      <th>room_name</th>\n",
       "      <th>building_no</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Amazon</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>War Room</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Office of CEO</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   room_no      room_name  building_no\n",
       "0        1         Amazon            1\n",
       "1        2       War Room            1\n",
       "2        3  Office of CEO            1"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "buildings = buildings.query('building_no != 2')\n",
    "\n",
    "building_remain = set(buildings.building_no)\n",
    "\n",
    "rooms = rooms.query('building_no in @building_remain')\n",
    "rooms"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "95bb858a-48a1-4647-9328-73d5949b7b0a",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Tips to find tables affected by MySQL ON DELETE CASCADE action"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ae7d6b23-8a13-413a-93b2-34c0d2aaf86f",
   "metadata": {},
   "source": [
    "Query this data from the referential_constraints in the information_schema database as follows:"
   ]
  },
  {
   "cell_type": "raw",
   "id": "88d828e3-c634-4909-af04-8d5ee98ff1fc",
   "metadata": {},
   "source": [
    "USE information_schema;\n",
    "\n",
    "SELECT \n",
    "    table_name\n",
    "FROM\n",
    "    referential_constraints\n",
    "WHERE\n",
    "    constraint_schema = 'database_name'\n",
    "        AND referenced_table_name = 'parent_table'\n",
    "        AND delete_rule = 'CASCADE'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "8fbca670-7e8e-4fbe-a587-2f455009cbe6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TABLE_NAME</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>rooms</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  TABLE_NAME\n",
       "0      rooms"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "connect_args={'ssl':{'fake_flag_to_enable_tls': True}}\n",
    "\n",
    "engine = create_engine('mysql+pymysql://namlq:abc123@localhost/information_schema',\n",
    "                       connect_args=connect_args, echo=False)\n",
    "\n",
    "string = '''\n",
    "SELECT table_name\n",
    "FROM referential_constraints\n",
    "WHERE constraint_schema = 'classicmodels'\n",
    "    AND referenced_table_name = 'buildings'\n",
    "    AND delete_rule = 'CASCADE'\n",
    ";'''\n",
    "\n",
    "pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "108bbdc0-8644-406d-a71d-c4ff19535d8d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7f2338f69580>"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "connect_args={'ssl':{'fake_flag_to_enable_tls': True}}\n",
    "engine = create_engine('mysql+pymysql://namlq:abc123@localhost/classicmodels',\n",
    "                       connect_args=connect_args, echo=False)\n",
    "\n",
    "engine.execute('DROP TABLE IF EXISTS buildings, rooms')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.15"
  },
  "toc-autonumbering": true,
  "toc-showmarkdowntxt": false,
  "toc-showtags": false
 },
 "nbformat": 4,
 "nbformat_minor": 5
}