As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns—the ones we don’t know we don’t know.
Chapter
14
Fear of the Unknown
In the example bugs database, the Accounts table has columns first_name and last_name. You can use an expression to format the user’s full name as a single column using the string concatenation operator:
| | SELECT first_name || ' ' || last_name AS full_name FROM Accounts; |
Suppose your boss asks you to modify the database to add the user’s middle initial to the table (perhaps two users have the same first name and last name, and the middle initial is a good way to avoid confusion). This is a pretty simple alteration. You also manually add the middle initials for a few users.
| | ALTER TABLE Accounts ADD COLUMN middle_initial CHAR(2); |
| | |
| | UPDATE Accounts SET middle_initial = 'J.' WHERE account_id = 123; |
| | UPDATE Accounts SET middle_initial = 'C.' WHERE account_id = 321; |
| | |
| | SELECT first_name || ' ' || middle_initial || ' ' || last_name AS full_name |
| | FROM Accounts; |
Suddenly, the application ceases to show any names. Actually, on a second look, you notice it isn’t universal. Only the names of users who have specified their middle initial appear normally; every else’s name is now blank.
What happened to everyone else’s names? Can you fix this before your boss notices and starts to panic, thinking you’ve lost data in the database?