// Validating numeric constraints MariaDB [sks]> select * from instructor; +-------+------------+------------+-----------+ | ID | name | dept_name | salary | +-------+------------+------------+-----------+ | 10101 | Srinivasan | Comp. Sci. | 71500.00 | | 12121 | Wu | Finance | 99000.00 | | 15151 | Mozart | Music | 44000.00 | | 22222 | Einstein | Physics | 114950.00 | | 32343 | El Said | History | 66000.00 | | 33456 | Gold | Physics | 105270.00 | | 45565 | Katz | Comp. Sci. | 82500.00 | | 58583 | Califieri | History | 68200.00 | | 76543 | Singh | Finance | 88000.00 | | 76766 | Crick | Biology | 79200.00 | | 83821 | Brandt | Comp. Sci. | 101200.00 | | 98345 | Kim | Elec. Eng. | 88000.00 | +-------+------------+------------+-----------+ 12 rows in set (0.001 sec) MariaDB [sks]> update instructor set salary = '28999' where name = 'Kim'; ERROR 4025 (23000): CONSTRAINT `instructor.salary` failed for `sks`.`instructor` MariaDB [sks]> update instructor set salary = '29000' where name = 'Kim'; ERROR 4025 (23000): CONSTRAINT `instructor.salary` failed for `sks`.`instructor` MariaDB [sks]> update instructor set salary = '29001' where name = 'Kim'; Query OK, 1 row affected (0.029 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [sks]> select * from instructor; +-------+------------+------------+-----------+ | ID | name | dept_name | salary | +-------+------------+------------+-----------+ | 10101 | Srinivasan | Comp. Sci. | 71500.00 | | 12121 | Wu | Finance | 99000.00 | | 15151 | Mozart | Music | 44000.00 | | 22222 | Einstein | Physics | 114950.00 | | 32343 | El Said | History | 66000.00 | | 33456 | Gold | Physics | 105270.00 | | 45565 | Katz | Comp. Sci. | 82500.00 | | 58583 | Califieri | History | 68200.00 | | 76543 | Singh | Finance | 88000.00 | | 76766 | Crick | Biology | 79200.00 | | 83821 | Brandt | Comp. Sci. | 101200.00 | | 98345 | Kim | Elec. Eng. | 29001.00 | +-------+------------+------------+-----------+ 12 rows in set (0.001 sec) MariaDB [sks]> update instructor set salary = '88000' where name = 'Kim'; Query OK, 1 row affected (0.004 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [sks]> select * from instructor; +-------+------------+------------+-----------+ | ID | name | dept_name | salary | +-------+------------+------------+-----------+ | 10101 | Srinivasan | Comp. Sci. | 71500.00 | | 12121 | Wu | Finance | 99000.00 | | 15151 | Mozart | Music | 44000.00 | | 22222 | Einstein | Physics | 114950.00 | | 32343 | El Said | History | 66000.00 | | 33456 | Gold | Physics | 105270.00 | | 45565 | Katz | Comp. Sci. | 82500.00 | | 58583 | Califieri | History | 68200.00 | | 76543 | Singh | Finance | 88000.00 | | 76766 | Crick | Biology | 79200.00 | | 83821 | Brandt | Comp. Sci. | 101200.00 | | 98345 | Kim | Elec. Eng. | 88000.00 | +-------+------------+------------+-----------+ 12 rows in set (0.001 sec) // Validating foreign key constraints // Inserting an invalid key generates an error MariaDB [sks]> update instructor set dept_name = 'Geography' where name = 'Kim'; ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`sks`.`instructor`, CONSTRAINT `instructor_ibfk_1` FOREIGN KEY (`dept_name`) REFERENCES `department` (`dept_name`) ON DELETE SET NULL) // Deleting a key from the foreign key table may have different outcomes MariaDB [sks]> select * from department; +------------+----------+-----------+ | dept_name | building | budget | +------------+----------+-----------+ | Biology | Watson | 90000.00 | | Comp. Sci. | Taylor | 100000.00 | | Elec. Eng. | Taylor | 85000.00 | | Finance | Painter | 120000.00 | | History | Painter | 50000.00 | | Music | Packard | 80000.00 | | Physics | Watson | 70000.00 | +------------+----------+-----------+ 7 rows in set (0.001 sec) MariaDB [sks]> select * from instructor; +-------+------------+------------+-----------+ | ID | name | dept_name | salary | +-------+------------+------------+-----------+ | 10101 | Srinivasan | Comp. Sci. | 71500.00 | | 12121 | Wu | Finance | 99000.00 | | 15151 | Mozart | Music | 44000.00 | | 22222 | Einstein | Physics | 114950.00 | | 32343 | El Said | History | 66000.00 | | 33456 | Gold | Physics | 105270.00 | | 45565 | Katz | Comp. Sci. | 82500.00 | | 58583 | Califieri | History | 68200.00 | | 76543 | Singh | Finance | 88000.00 | | 76766 | Crick | Biology | 79200.00 | | 83821 | Brandt | Comp. Sci. | 101200.00 | | 98345 | Kim | Elec. Eng. | 88000.00 | +-------+------------+------------+-----------+ 12 rows in set (0.001 sec) // Deleting an entry from department generates a null value in instructor // This is because the complete foreign key definition in instructor was // the following // foreign key (dept_name) references department (dept_name) // on delete set null MariaDB [sks]> delete from department where dept_name = 'Physics'; Query OK, 1 row affected (0.004 sec) MariaDB [sks]> select * from department; +------------+----------+-----------+ | dept_name | building | budget | +------------+----------+-----------+ | Biology | Watson | 90000.00 | | Comp. Sci. | Taylor | 100000.00 | | Elec. Eng. | Taylor | 85000.00 | | Finance | Painter | 120000.00 | | History | Painter | 50000.00 | | Music | Packard | 80000.00 | +------------+----------+-----------+ 6 rows in set (0.001 sec) MariaDB [sks]> select * from instructor; +-------+------------+------------+-----------+ | ID | name | dept_name | salary | +-------+------------+------------+-----------+ | 10101 | Srinivasan | Comp. Sci. | 71500.00 | | 12121 | Wu | Finance | 99000.00 | | 15151 | Mozart | Music | 44000.00 | | 22222 | Einstein | NULL | 114950.00 | | 32343 | El Said | History | 66000.00 | | 33456 | Gold | NULL | 105270.00 | | 45565 | Katz | Comp. Sci. | 82500.00 | | 58583 | Califieri | History | 68200.00 | | 76543 | Singh | Finance | 88000.00 | | 76766 | Crick | Biology | 79200.00 | | 83821 | Brandt | Comp. Sci. | 101200.00 | | 98345 | Kim | Elec. Eng. | 88000.00 | +-------+------------+------------+-----------+ 12 rows in set (0.001 sec) // Deleting an entry from course deletes the row in section // This is a "cascading delete". The complete foreign key definition // in section was the following // foreign key (course_id) references course (course_id) // on delete cascade, MariaDB [sks]> select * from section; +-----------+--------+----------+------+----------+-------------+--------------+ | course_id | sec_id | semester | year | building | room_number | time_slot_id | +-----------+--------+----------+------+----------+-------------+--------------+ | BIO-101 | 1 | Summer | 2017 | Painter | 514 | B | | BIO-301 | 1 | Summer | 2018 | Painter | 514 | A | | CS-101 | 1 | Fall | 2017 | Packard | 101 | H | | CS-101 | 1 | Spring | 2018 | Packard | 101 | F | | CS-190 | 1 | Spring | 2017 | Taylor | 3128 | E | | CS-190 | 2 | Spring | 2017 | Taylor | 3128 | A | | CS-315 | 1 | Spring | 2018 | Watson | 120 | D | | CS-319 | 1 | Spring | 2018 | Watson | 100 | B | | CS-319 | 2 | Spring | 2018 | Taylor | 3128 | C | | CS-347 | 1 | Fall | 2017 | Taylor | 3128 | A | | EE-181 | 1 | Spring | 2017 | Taylor | 3128 | C | | FIN-201 | 1 | Spring | 2018 | Packard | 101 | B | | HIS-351 | 1 | Spring | 2018 | Painter | 514 | C | | MU-199 | 1 | Spring | 2018 | Packard | 101 | D | | PHY-101 | 1 | Fall | 2017 | Watson | 100 | A | +-----------+--------+----------+------+----------+-------------+--------------+ 15 rows in set (0.001 sec) MariaDB [sks]> select * from course; +-----------+----------------------------+------------+---------+ | course_id | title | dept_name | credits | +-----------+----------------------------+------------+---------+ | BIO-101 | Intro. to Biology | Biology | 4 | | BIO-301 | Genetics | Biology | 4 | | BIO-399 | Computational Biology | Biology | 3 | | CS-101 | Intro. to Computer Science | Comp. Sci. | 4 | | CS-190 | Game Design | Comp. Sci. | 4 | | CS-315 | Robotics | Comp. Sci. | 3 | | CS-319 | Image Processing | Comp. Sci. | 3 | | CS-347 | Database System Concepts | Comp. Sci. | 3 | | EE-181 | Intro. to Digital Systems | Elec. Eng. | 3 | | FIN-201 | Investment Banking | Finance | 3 | | HIS-351 | World History | History | 3 | | MU-199 | Music Video Production | Music | 3 | | PHY-101 | Physical Principles | NULL | 4 | +-----------+----------------------------+------------+---------+ 13 rows in set (0.001 sec) MariaDB [sks]> delete from course where course_id = 'CS-347'; Query OK, 1 row affected (0.033 sec) MariaDB [sks]> select * from course; +-----------+----------------------------+------------+---------+ | course_id | title | dept_name | credits | +-----------+----------------------------+------------+---------+ | BIO-101 | Intro. to Biology | Biology | 4 | | BIO-301 | Genetics | Biology | 4 | | BIO-399 | Computational Biology | Biology | 3 | | CS-101 | Intro. to Computer Science | Comp. Sci. | 4 | | CS-190 | Game Design | Comp. Sci. | 4 | | CS-315 | Robotics | Comp. Sci. | 3 | | CS-319 | Image Processing | Comp. Sci. | 3 | | EE-181 | Intro. to Digital Systems | Elec. Eng. | 3 | | FIN-201 | Investment Banking | Finance | 3 | | HIS-351 | World History | History | 3 | | MU-199 | Music Video Production | Music | 3 | | PHY-101 | Physical Principles | NULL | 4 | +-----------+----------------------------+------------+---------+ 12 rows in set (0.001 sec) MariaDB [sks]> select * from section; +-----------+--------+----------+------+----------+-------------+--------------+ | course_id | sec_id | semester | year | building | room_number | time_slot_id | +-----------+--------+----------+------+----------+-------------+--------------+ | BIO-101 | 1 | Summer | 2017 | Painter | 514 | B | | BIO-301 | 1 | Summer | 2018 | Painter | 514 | A | | CS-101 | 1 | Fall | 2017 | Packard | 101 | H | | CS-101 | 1 | Spring | 2018 | Packard | 101 | F | | CS-190 | 1 | Spring | 2017 | Taylor | 3128 | E | | CS-190 | 2 | Spring | 2017 | Taylor | 3128 | A | | CS-315 | 1 | Spring | 2018 | Watson | 120 | D | | CS-319 | 1 | Spring | 2018 | Watson | 100 | B | | CS-319 | 2 | Spring | 2018 | Taylor | 3128 | C | | EE-181 | 1 | Spring | 2017 | Taylor | 3128 | C | | FIN-201 | 1 | Spring | 2018 | Packard | 101 | B | | HIS-351 | 1 | Spring | 2018 | Painter | 514 | C | | MU-199 | 1 | Spring | 2018 | Packard | 101 | D | | PHY-101 | 1 | Fall | 2017 | Watson | 100 | A | +-----------+--------+----------+------+----------+-------------+--------------+ 14 rows in set (0.001 sec) // Cannot delete a course that appears as prereq_id in prereq // The foreign key defintion for prereq_id does not specify what // is to be done on delete, so delete is not allowed. Must first // remove the entry from prereq and then from course. // foreign key (course_id) references course (course_id) // on delete cascade, // foreign key (prereq_id) references course (course_id) MariaDB [sks]> delete from course where course_id = 'CS-101'; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`sks`.`prereq`, CONSTRAINT `prereq_ibfk_2` FOREIGN KEY (`prereq_id`) REFERENCES `course` (`course_id`)) MariaDB [sks]> select * from prereq; +-----------+-----------+ | course_id | prereq_id | +-----------+-----------+ | BIO-301 | BIO-101 | | BIO-399 | BIO-101 | | CS-190 | CS-101 | | CS-315 | CS-101 | | CS-319 | CS-101 | | EE-181 | PHY-101 | +-----------+-----------+ 6 rows in set (0.001 sec) // Joins // "... join using (columns) ... "is like natural join // matching columns are collapsed MariaDB [sks]> select * from course join prereq using (course_id); +-----------+---------------------------+------------+---------+-----------+ | course_id | title | dept_name | credits | prereq_id | +-----------+---------------------------+------------+---------+-----------+ | BIO-301 | Genetics | Biology | 4 | BIO-101 | | BIO-399 | Computational Biology | Biology | 3 | BIO-101 | | CS-190 | Game Design | Comp. Sci. | 4 | CS-101 | | CS-315 | Robotics | Comp. Sci. | 3 | CS-101 | | CS-319 | Image Processing | Comp. Sci. | 3 | CS-101 | | EE-181 | Intro. to Digital Systems | Elec. Eng. | 3 | PHY-101 | +-----------+---------------------------+------------+---------+-----------+ 6 rows in set (0.001 sec) // "... join on condition ..." is like theta-join in relational algebra // matching columns are duplicated in output MariaDB [sks]> select * from course join prereq on course.course_id = prereq.course_id ; +-----------+---------------------------+------------+---------+-----------+-----------+ | course_id | title | dept_name | credits | course_id | prereq_id | +-----------+---------------------------+------------+---------+-----------+-----------+ | BIO-301 | Genetics | Biology | 4 | BIO-301 | BIO-101 | | BIO-399 | Computational Biology | Biology | 3 | BIO-399 | BIO-101 | | CS-190 | Game Design | Comp. Sci. | 4 | CS-190 | CS-101 | | CS-315 | Robotics | Comp. Sci. | 3 | CS-315 | CS-101 | | CS-319 | Image Processing | Comp. Sci. | 3 | CS-319 | CS-101 | | EE-181 | Intro. to Digital Systems | Elec. Eng. | 3 | EE-181 | PHY-101 | +-----------+---------------------------+------------+---------+-----------+-----------+ 6 rows in set (0.001 sec) // natural join // Be careful when doing natural join across multiple relations MariaDB [sks]> select name, title from student natural join takes natural join course; +----------+----------------------------+ | name | title | +----------+----------------------------+ | Zhang | Intro. to Computer Science | | Shankar | Intro. to Computer Science | | Shankar | Game Design | | Shankar | Robotics | | Brandt | World History | | Chavez | Investment Banking | | Williams | Intro. to Computer Science | | Williams | Game Design | | Sanchez | Music Video Production | | Brown | Intro. to Computer Science | | Brown | Image Processing | | Aoi | Intro. to Digital Systems | | Tanaka | Intro. to Biology | | Tanaka | Genetics | +----------+----------------------------+ 14 rows in set (0.001 sec) MariaDB [sks]> select name, title from student natural join takes, course where takes.course_id = course.course_id; +----------+----------------------------+ | name | title | +----------+----------------------------+ | Tanaka | Intro. to Biology | | Tanaka | Genetics | | Zhang | Intro. to Computer Science | | Shankar | Intro. to Computer Science | | Levy | Intro. to Computer Science | | Williams | Intro. to Computer Science | | Brown | Intro. to Computer Science | | Bourikas | Intro. to Computer Science | | Levy | Intro. to Computer Science | | Shankar | Game Design | | Williams | Game Design | | Shankar | Robotics | | Bourikas | Robotics | | Levy | Image Processing | | Brown | Image Processing | | Aoi | Intro. to Digital Systems | | Chavez | Investment Banking | | Brandt | World History | | Sanchez | Music Video Production | | Peltier | Physical Principles | +----------+----------------------------+ 20 rows in set (0.001 sec) // Create a view MariaDB [sks]> create view lim_inst as select ID,name,dept_name from instructor; Query OK, 0 rows affected (0.005 sec) MariaDB [sks]> select * from lim_inst; +-------+------------+------------+ | ID | name | dept_name | +-------+------------+------------+ | 10101 | Srinivasan | Comp. Sci. | | 12121 | Wu | Finance | | 15151 | Mozart | Music | | 22222 | Einstein | NULL | | 32343 | El Said | History | | 33456 | Gold | NULL | | 45565 | Katz | Comp. Sci. | | 58583 | Califieri | History | | 76543 | Singh | Finance | | 76766 | Crick | Biology | | 83821 | Brandt | Comp. Sci. | | 98345 | Kim | Elec. Eng. | +-------+------------+------------+ 12 rows in set (0.001 sec) // Drop a view MariaDB [sks]> drop view lim_inst; Query OK, 0 rows affected (0.005 sec) MariaDB [sks]> select * from lim_inst; ERROR 1146 (42S02): Table 'sks.lim_inst' doesn't exist // Recreate the view MariaDB [sks]> create view lim_inst as select ID,name,dept_name from instructor; Query OK, 0 rows affected (0.027 sec) MariaDB [sks]> select * from lim_inst; +-------+------------+------------+ | ID | name | dept_name | +-------+------------+------------+ | 10101 | Srinivasan | Comp. Sci. | | 12121 | Wu | Finance | | 15151 | Mozart | Music | | 22222 | Einstein | NULL | | 32343 | El Said | History | | 33456 | Gold | NULL | | 45565 | Katz | Comp. Sci. | | 58583 | Califieri | History | | 76543 | Singh | Finance | | 76766 | Crick | Biology | | 83821 | Brandt | Comp. Sci. | | 98345 | Kim | Elec. Eng. | +-------+------------+------------+ 12 rows in set (0.001 sec) // Modify the table from which the view was derived MariaDB [sks]> delete from instructor where dept_name is null; Query OK, 2 rows affected (0.004 sec) // The view is automatically updated MariaDB [sks]> select * from lim_inst; +-------+------------+------------+ | ID | name | dept_name | +-------+------------+------------+ | 10101 | Srinivasan | Comp. Sci. | | 12121 | Wu | Finance | | 15151 | Mozart | Music | | 32343 | El Said | History | | 45565 | Katz | Comp. Sci. | | 58583 | Califieri | History | | 76543 | Singh | Finance | | 76766 | Crick | Biology | | 83821 | Brandt | Comp. Sci. | | 98345 | Kim | Elec. Eng. | +-------+------------+------------+ 10 rows in set (0.001 sec) // MariaDB is case insensitve, may not be true across all SQL implementations MariaDB [sks]> select id from lim_inst; +-------+ | ID | +-------+ | 76766 | | 10101 | | 45565 | | 83821 | | 98345 | | 12121 | | 76543 | | 32343 | | 58583 | | 15151 | +-------+ 10 rows in set (0.001 sec)