![]() Marks of the student (int) is a regular column. Whenever rows in the parent (referenced) table are deleted (or updated), the respective rows of the child (referencing) table with a matching. Primary key: Enrollment number (int), name (varchar(20)) Name (varchar(20)), email-id (varchar(20)), and State of the student (varchar(20)) are regular columns. Setting up DatabaseĪs we have three tables, T1, T2, and T3, each table should be in the following format. ![]() To do so, we will define our database more efficiently. The essential syntax for a defining a foreign key constraint in a CREATE TABLE or ALTER TABLE statement includes the following: CONSTRAINT symbol FOREIGN KEY indexname (colname. Using a similar example, we will learn how to use CASCADE in SQL while deleting/updating an entry in the parent and child table. A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. CASCADE : Delete or update the row from the parent table and automatically delete or update the matching rows in the child table. With a single command, we can change all three tables simultaneously. We use the keyword CASCADE to overcome this problem in SQL queries. 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. As the student will be shifted to another class, so his information needs to be deleted.Ĭase 2: If a student wants to change his name/enrollment number, he has to update all three tables, i.e., T1, T2, and T3. 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.įollowing is the design of our MySQL database (arrangement of tables).Ĭase 1: If a student wants to change his class, he must delete his name from all the tables, i.e., T1, T2, and T3. Both tables, T2 and T3, have three columns, i.e., enrollment number, name of the students, and marks of the student. Table T3 contains the marks of all the students in the Science subject. Table T2 contains the marks of all the students in the Mathematics subject. Let us now consider two more tables, i.e., T2 and T3. Table T1 has four columns, i.e., Enrollment number, name, email, and state of the student. Let us take an example in layman's terms to understand the usage of cascade keyword in SQL.Ĭonsider a table named T1 which contains the data of all the students in a class.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |