Quiz

1. Is it possible to do a cascading update?

   Yes, for sure:

   
       create table employee (
       emp_id int not null auto_increment,
       name varchar(50),
       ...
       primary key (emp_id),
       index (name)
       foreign key (dept_id) references dept(dept_id)
           on update cascade
           on delete set null
   
   

2. In what scenario would you want to use "set null" for updated or deleted keys? If you cannot insert a row into the table without this key being set, why are you allowed to "set null"?

   Great question! I think you might be confusing primary keys with foreign keys

   A primary key cannot be null. For example, every employee must have an emp_id

   A foreign key can be allowed to be null. For example, if a re-org means that an employee's department no longer exists, but we aren't laying-off the employee, we might set their dept_id to null until such time as they are assigned to a new department.

3. In the album/track example, what's the real difference between ON UPDATE RESTRICT and ON DELETE RESTRICT? Like, when would "update restrict" actually matter if the album ID changed?

   UPDATE is what to do if the FK changes; DELETE is what to do if the FK (row) is deleted

   If we had on delete restrict, we would not be allowed to delete the album if it has tracks.

   If we had on update restrict, we would not be allowed to change the album ID if it has tracks.

   That might be what we want, or it might not.

4. I am very confused about how InnoDB works. Is it a database or does it create a table? / Please explain innoDB more! What is it, I was not really sure about any of the question options.

   Great question!

   Think back to CS 230. You learned several implementations for graphs, including adjacency matrices and adjacency lists. They support the same operations, but they have pros/cons. You choose the one that fits your problem. InnoDB is like that.

   InnoDB is a data structure for a table. There are others and, in our version of MySQL, InnoDB is not the default. We have to choose it on purpose.

5. Should we not just always use innoDB to ensure referential integrity?

   Maybe!

6. Why can CSV files sometimes end up with the wrong number of columns when imported into a spreadsheet or database?

   If there's a comma in a field. Suppose we are importing our pet data:

    NAME, BREED, WEIGHT
    Tippy, mutt, 20
    Salem, german shepherd, dachshund, 30
   

   Oops.

7. I think this makes sense! / I have no questions for now. I think I have a good understanding of the material covered.

   Great!