Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      On Delete Cascade

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

      On Delete Cascade

      The MySQL ON DELETE CASCADE Constraint

      The MySQL ON DELETE CASCADE constraint ensures that when a row in the parent table is deleted, all related rows in the child table are automatically deleted as well. This constraint helps maintain referential integrity between two tables that are connected through a foreign key relationship.
      If we do not use this constraint, the database server will, by default, prevent us from deleting data in a table if it is referenced by other tables.

      Example

      Creating the Parent Table
      First, let us create the parent table with the name PERSONS using the following query 
      CREATE TABLE PERSONS(
         P_ID int primary key,
         P_NAME varchar(40),
         P_AGE int
      );
      Now, let us insert some values into the above created table using the INSERT statement as shown below 
      INSERT INTO PERSONS VALUES 
      (1, "Priya", 29),
      (2, "Sarah", 20),
      (3, "Varun", 26),
      (4, "Dev", 25),
      (5, "Ram", 31),
      (6, "Aarohi", 34);
      The PERSONS table obtained is as shown below
      P_ID
      P_NAME
      P_AGE
      1
      Priya
      29
      2
      Sarah
      20
      3
      Varun
      26
      4
      Dev
      25
      5
      Ram
      31
      6
      Aarohi
      34
      Creating the Child Table −
      Now, let us create a child table named Films_watched with the ON DELETE CASCADE constraint. In this table, the P_ID column is a foreign key referencing the P_ID column in the Persons table 
      CREATE TABLE Films_watched (
         P_ID INT,
         F_NO INT,
         F_NAME varchar(40),
         PRIMARY KEY(P_ID,F_NO),
         FOREIGN KEY(P_ID)
         REFERENCES PERSONS(P_ID)
         ON DELETE CASCADE
      );
      Now, we are inserting rows into the Films_watched table 
      INSERT INTO Films_watched VALUES 
      (1, 130, "RRR"),
      (2, 131, "Bahubali"),
      (3, 132, "Pushpa"),
      (3, 133, "KGF"),
      (3, 134, "Salaar"),
      (6, 135, "Karthikeya");
      The Films_watched table produced is as follows
      P_ID
      F_NO
      F_NAME
      1
      130
      RRR
      2
      131
      Bahubali
      3
      132
      Pushpa
      3
      133
      KGF
      3
      134
      Salaar
      6
      135
      Karthikeya
      Deleting a Record from the Parent Table
      As we can see in the above table, we have three films that has been watched by the person with P_ID = 3. Here, we are deleting the person with P_ID = 3 from the PERSONS (parent) table 
      DELETE FROM PERSONS WHERE P_ID = 3;
      Following is the output obtained 
      Query OK, 1 row affected (0.01 sec)
      After this deletion, let us check the data in both the Persons and Films_watched tables.
      Querying Data from the Persons Table
      To see the remaining records in the Persons table, use the following SELECT query 
      SELECT * FROM PERSONS;
      We can see in the table below, the row with P_ID = 3 is deleted
      P_ID
      P_NAME
      P_AGE
      1
      Priya
      29
      2
      Sarah
      20
      4
      Dev
      25
      5
      Ram
      31
      6
      Aarohi
      34
      Querying Data from the Films_watched Table
      Lastly, you can check the data in the Films_watched table 
      SELECT * FROM Films_watched;

      Output

      We can see in the output below that all related records with P_ID = 3 have been automatically deleted
      P_ID
      F_NO
      F_NAME
      1
      130
      RRR
      2
      131
      Bahubali
      6
      135
      Karthikeya