[SQL]: NULL & NOT NULL

SQL

09/22/2019

NULL

Null is unknown value or "empty"
Trigerring NULL

SQL

INSERT INTO dogs(name)
VALUES("OSHKOSH");
--OR
INSERT INTO dogs()
VALUES();
--Check
SELECT * FROM dogs;

Output:

TEXT

+---------+------+
| name    | age  |
+---------+------+
| Oshkosh | NULL |
| NULL    | NULL |
+---------+------+

Recall:

SQL

SHOW COLUMNS FROM dogs;

TEXT

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(100) | YES  |     | NULL    |       |
| age   | int(11)      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

NULL is allowed

NOT_NULL

To prevent above situation where a data is inserted as NULL, we can define each column to NOT NULL

SQL

CREATE TABLE dogs2
(
  name VARCHAR(50) NOT NULL,
  age INT NOT NULL
);

SQL

DESC dogs2;

Output:

TEXT

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | int(11)     | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

Now, try

SQL

INSERT INTO dogs2(name) VALUES("Bunny");
INSERT INTO dogs(age) VALUES('17');

Output:

TEXT

mysql> SELECT * FROM dogs2;
+-------+-----+
| name  | age |
+-------+-----+
| Bunny |   0 |
|       |  17 |
+-------+-----+

Now the default value is set to an empty string and 0

Setting a default value

SQL

CREATE TABLE dogs3
(
  name VARCHAR(50) DEFAULT 'noname',
  age INT DEFAULT -1
);

Now test:

SQL

INSERT INTO dogs3() VALUES(); -- setting no values for all columns
SELECT * FROM dogs3;

Output:

TEXT

+--------+------+
| name   | age  |
+--------+------+
| noname |   -1 |
+--------+------+

How about this?

SQL

CREATE TABLE dogs4
(
  name VARCHAR(50) NOT NULL DEFAULT 'noname',
  age INT NOT NULL DEFAULT -1
);

This may seem redundant but it is not
This prevents inserting/setting NULL to the table
- It will throw an error when NULL is inserted

WRITTEN BY

Ellis Min

Keeping a record

GitHub