MySQL: Warnings, Null/Not Null, & Setting Defaults (Uncle Roger Ver) | Janifer

In this post I will be discussing warnings, null/ not null and setting defaults in MySQL using the Feynman technique and YouTube sensation Uncle Roger.

This post is part of my Feynman Technique & 20 Hour Method challenge. For more info, please click here.

Recently we used YouTube sensation, Uncle Roger, to talk about JavaScript Functions, and normally I’d use another teaching vehicle, however, Nigel Ng, the creator of the character recently released a SPECTACULARLY bad fried rice review and I thought “what better way to explain MySQL warnings, nulls, and defaults than with the below video:

A Brief Review of MySQL:

MySQL is a relational database language, which means it recognizes connections between stored pieces of information. Because it’s a database, we’re talking tables… lots and lots of tables.

Enough about that. Let’s get down to the nitty-gritty.

Four Kinds of Fried Rice:

I’ll be creating four tables called friedrice1-4. You can consider it Uncle Roger’s greatest hits. I’ll be using these fried rice tables as my code samples.

Here’s friedrice1. It’s your standard simple code.

CREATE TABLE friedrice1
(
ingredients VARCHAR(20),
amount INT
);

To make it clear, we’re going to use at least one empty value entry in each of these tables to show you what happens:

INSERT INTO friedrice1 () VALUES ();

NULL is a Default in MySQL

Because we did not include defaults in our friedrice1 table, this entry was accepted into our table, however, both the ingredients and the amount entries are listed as “null”. If there’s no default value established by the creator of the table, “null” becomes the default. Null does not equal to 0, but is equal to “no entry”.

INSERT INTO friedrice1 (ingredients, amount) VALUES ('oil', 1);

The code above is established as a normal entry with ingredients and amount, so there’s no warning (kickback) for this entry.

Warnings:

INSERT INTO friedrice1 (ingredients, amount) VALUES ('uncooked unwashed hard rice', 5);

The above code does produce a warning. Due to the established “varchar(20)” (20 character) limit, the ingredient name exceeded that amount. This set off the warning shown below. To view what the warning was, we proceed to type in “show warnings;”

+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning | 1265 | Data truncated for column 'ingredients' at row 1 |
+---------+------+--------------------------------------------------+
1 row in set (0.00 sec)

We go ahead and enter two more entries into this table:

INSERT INTO friedrice1 (ingredients, amount) VALUES (null, 1),

('ginger', null);

Both these entries get added without incident, however, our last doesn’t follow suit:

INSERT INTO friedrice1 (ingredients, amount) VALUES ('charred garlic', unknown);

We immediately get the following error message:

ERROR 1054 (42S22): Unknown column 'unknown' in 'field list'

Because “unknown” was not an integer (int), the system refused the entry. The outcome of our table looks like this, after calling it with “show * from friedrice1;“

+----------------------+--------+
| ingredients          | amount |
+----------------------+--------+
| NULL                 |   NULL |
| oil                  |      1 |
| uncooked unwashed ha |      5 |
| NULL                 |      1 |
| ginger               |   NULL |
+----------------------+--------+
5 rows in set (0.01 sec)

Our first entry comes back with null/null due to the blank fields. Oil was normal, but if you see entry #3 that shot back a warning, we now see that the system shortened our entry for “uncooked unwashed hard rice”. It only displayed up until our 20 character limit. As for the final two, we used “null” values, and the table accepted them. Finally, the “charred garlic” that came back with an immediate error did not show up in the end. Even MySQL wouldn’t accept it. Since it did produce an error message (not a warning), it did not add any information to the table.

NOT NULL Setting:

CREATE TABLE friedrice2
(
ingredients VARCHAR(20) NOT NULL,
amount INT NOT NULL
);

Table #2 has a prerequisite of “not null” included in its rules. This will prevent the use of “null” as an option or default. As we did previously, we’ll use a blank entry to see how the system responds:

INSERT INTO friedrice2 () VALUES ();

We are given two warnings for this. To view what the warnings are, we type in “show warnings;”. If this isn’t done immediately after the warning prompt, MySQL will not store the information and it will not be accessible at a later time. Here’s our warning for the entry above:

+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning | 1364 | Field 'ingredients' doesn't have a default value |
| Warning | 1364 | Field 'amount' doesn't have a default value      |
+---------+------+--------------------------------------------------+
2 rows in set (0.00 sec)

For the next entry we attempt to only add one value:

INSERT INTO friedrice2 (amount) VALUES (2);

…and we get the following warning:

+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning | 1364 | Field 'ingredients' doesn't have a default value |
+---------+------+--------------------------------------------------+
1 row in set (0.00 sec)

Instead of using empty entries, we attempt to use “null” for the following two items and both produce similar error messages:

INSERT INTO friedrice2 (ingredients, amount) VALUES ('colander', NULL);
INSERT INTO friedrice2 (ingredients, amount) VALUES (NULL, 1);

…and we get back this:

ERROR 1048 (23000): Column 'amount' cannot be null
ERROR 1048 (23000): Column 'ingredients' cannot be null

The outcome of our table, after all our hiccups, looks like this:

+-------------+--------+
| ingredients | amount |
+-------------+--------+
|             |      0 |
|             |      2 |
+-------------+--------+
2 rows in set (0.00 sec)

The two entries that had warnings were added to the table. Since the “not null” setting was in place, empty values didn’t produce a “null” entry as it did in friedrice1. Instead a blank spot in the varchar column and a 0 in the int. This was the outcome for the blank entry.

In friedrice3 we replaced the “not null” with a default value. In this case, as in the previous attempts, empty entries should be replaced by the default values and warnings should be gone:

INSERT INTO friedrice3 () VALUES ();

INSERT INTO friedrice3 (ingredients) VALUES ('water');

INSERT INTO friedrice3 (ingredients, amount) VALUES ('colander', NULL);

All three entries did not produce a warning or a kickback, even entry #3 had “null” as a listed amount. This is what the final table looks like:

+-------------+--------+
| ingredients | amount |
+-------------+--------+
| unnamed     |    999 |
| water       |    999 |
| colander    |   NULL |
+-------------+--------+
3 rows in set (0.00 sec)

As we suspected, empty entries auto-filled in holes with our default value. The last amount that we listed as “null” overrode our default value. Since there was no “not null” setting in our table, the entry was accepted.

Setting Defaults:

For our friedrice4 table, we added both the “not null” and “default” settings to the table.

CREATE TABLE friedrice4
(
ingredients VARCHAR(20) NOT NULL DEFAULT 'unnamed',
amount INT NOT NULL DEFAULT 999
);

We add our control entry, the blank, to see how MySQL responds:

INSERT INTO friedrice4 () VALUES ();

No kickback or warnings from this entry. Now let’s try a “null” entry.

INSERT INTO friedrice4 (ingredients, amount) VALUES ('msg', NULL);

This produces an immediate error message:

ERROR 1048 (23000): Column 'amount' cannot be null

We can see by this that the “not null” is working. Although that may be sad for Uncle Roger, it works perfectly for our needs.

INSERT INTO friedrice4 (amount) VALUES (1);

INSERT INTO friedrice4 (ingredients) VALUES ('spring onion');

As for our last two entries, we only added one value each and they came back with neither a warning nor an error.

Our final table now looks like this:

+--------------+--------+
| ingredients  | amount |
+--------------+--------+
| unnamed      |    999 |
| unnamed      |      1 |
| spring onion |    999 |
+--------------+--------+
3 rows in set (0.00 sec)

For all the blank value entries, the table automatically added our default values to fill in those spaces. All other values that we entered were accepted except the entries that received immediate errors, as they did in our previous tables, also did not show up on our final table.

Happy coding, and please, PLEASE, spare yourself from eating this woman’s fried rice. If anything, you’ll spare yourself a run to the hospital for food poisoning. I’m not kidding (rice not properly cooked can cause food poisoning).