Quiz

1. Can a column have more than one datatypes in it?

   Nope. It's a good question, because there are programming languages with "union" datatypes: "this variable holds either an X or a Y" but AFAIK, MySQL forces you to choose.

2. Why does MySQL prevent duplicate entries when using a primary key, but not when using a non-primary key column?

   Well, that's part of the purpose of a primary key. After all, it would be possible for two Wellesley students to have the same address (duplicate entries) but not the same ID.

   The primary key is, by definition, a unique identifier. So, enforcing uniqueness is part of its raison d'être

3. What happens to existing rows in a table in the instance where you need to update a column, e.g, change name?

   You can change the name without losing any data. Changing the datatype is more problematic.

4. Can you change a column's datatype once the table is already created? If so, what happens to the values already in the column?

   Yes. We'll learn how to do that later, but for now, I suggest you drop the table, re-create it, and re-load the data.

   If you've changed the datatype of a column, say from string to int, and you have "fred" in the column, MySQL won't be able to convert it. So, you'll have to do that yourself or the data is lost.

5. Are enum types case sensitive? By this, I mean when inserting rows into a table, if the case does not match the enum options for a column, will you get an error?

   Great question! The documentation says that it depends on the character set and collocation (how strings are sorted). So, that means "yes, probably" since we are using UTF8.

   Honestly, though, I would avoid that. Don't create an ENUM where you have both "dog" and "Dog" as possible, but different, values.

6. If you are making a database table and know a column will use an integer value, but you aren't sure how big they will get, what should you use as the default datatype? What happens if you try to store a value larger than 255 in a tinyint datatype column?

   Another great question! It's worth taking a minute to try to come up with an estimate. Err on the side of being generous, but not crazy. A 4-byte INT has a very large range. It's unlikely you would need an 8-byte BIGINT.

   See integer types

   Let's talk about IPv4 for a minute and also Y2K.

7. Is there a way to automate an "are you sure" check to avoid accidental database deletion when something like "delete from {db};" is run?

   Yes! Believe it or not, you can start MySQL with a special flag that requires you to use a primary key when deleting rows. It's called --i-am-a-dummy

   That's a double hyphen at the beginning.

   
   [cs304guest@cs ~ ] mysql --i-am-a-dummy
   MariaDB [cs304_db]> select * from owner9;
   +-----+------------------+-----------------------+-------+---------+
   | oid | name             | address               | phone | balance |
   +-----+------------------+-----------------------+-------+---------+
   |   1 | Homer Simpson    | 742 Evergreen Terrace | NULL  |   88.95 |
   |   2 | Harry Potter     | NULL                  | NULL  |       0 |
   |   3 | Ron Weasley      | NULL                  | NULL  |       0 |
   |   4 | Hermione Granger | NULL                  | NULL  |       0 |
   +-----+------------------+-----------------------+-------+---------+
   4 rows in set (0.001 sec)
   
   MariaDB [cs304_db]> delete from owner9;
   ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
   
   
   

8. Can you explain the line 'uid int not null auto_increment'? Why does it auto increment on 'not null' instead of 'null'.

   Ah, those are two separate things that just happen to be next to each other.

   not null is a rule about what kind of values can be in the column

   auto_increment says that if the value is not supplied, use the auto_increment feature

9. Why would we not always just use varchar for strings like emails, passwords, etc..

   We probably would. But if speed were an issue, and they weren't too long, we might use char()

10. Why is fixed-length data faster to access (in the context of char versus varchar)?

    For the same reason that arrays are faster to access than linked lists.

    If you know that the thing you want is 88 bytes past the beginning of the record, you can jump directly there.

    But if all you know is that there's some text you have to skip, with an end-of-text marker, you have to look at each byte and look for the end-of-text marker.

11. If SQL has no "undelete", is there any other way that we could possibly recover some mistake made??

    Yes, you can dump the data to a file before doing some risky update. I do that all the time.

    See mysqldump

12. How to dump existing data to sql database?

    The opposite of "dump" is "load". We'll learn how to load from files, such as CSV files, next time.

13. None so far! / no questions, thank you! / None! This builds nicely on our current SQL knowledge.

    I'm glad!