NOT NULL CONSTRAINT and DEFAULT Clause in SQL

NOT NULL Constraint

Suppose you don’t want a particular attribute (column) of a relation (table) to have NULL value. To do this you need to apply NOT NULL constraint to this attribute.

DEFAULT Clause

Suppose you want to insert a new tuple (row) in a relation and want a default value for a particular attribute. To do this you need to apply a DEFAULT clause to this attribute.

An attribute having neither DEFAULT clause nor NOT NULL constraint would have NULL as default value when you will add a new tuple in the relation.

There are four possibilities.

1. An attribute has neither DEFAULT clause nor NOT NULL constraint specified.
2. An attribute has only DEFAULT clause specified.
3. An attribute has only NOT NULL constraint specified.
4. An attribute has both NOT NULL constraint and DEFAULT clause specified.

We can write this in table form.

-	-
-	DEFAULT
NOT NULL	-
NOT NULL	DEFAULT

Let’s try these possibilities in MySQL database management system.

First create a database. (see Figure 1)

CREATE DATABASE facts;

Now let’s try the third possibility. Create a table called CONTINENT3.

CREATE TABLE continent3 (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL, PRIMARY KEY(id) );

Now insert a new row in the table CONTINENT3.

INSERT INTO continent3 () VALUES ();

Figure 1

This INSERT INTO statement is successfully executed. We didn’t expect this behavior. Since we have applied NOT NULL constraint to name attribute and there is no default value, this statement shouldn’t have been executed.

The reason for this is that MySQL strict mode was not enabled. Use the following statement to enable MySQL strict mode. (see Figure 2)

SET sql_mode='STRICT_TRANS_TABLES';

After this try to execute the earlier INSERT INTO statement again. This time we would get an error as expected. The error is ERROR 1364 (HY000): Field 'name' doesn't have a default value

Figure 2: Setting the variable 'sql_mode'

Let’s try fourth possibility. Create a table called CONTINENT4. (see Figure 3)

CREATE TABLE continent4 (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL DEFAULT 'Earth', PRIMARY KEY(id) );

This time I’ve also specified DEFAULT value for name attribute. Insert a new row in table CONTINENT4. You see, there is neither an error nor any warning.

Figure 3

We can also specify an empty string as DEFAULT clause. Create a new table CONTINENT5.

CREATE TABLE continent5 (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL DEFAULT '', PRIMARY KEY(id) );

Execute this DESCRIBE command.

DESCRIBE continent5;

In the row for name attribute (see Figure 4), under the column Default, there is nothing being displayed. This shows that while defining table continent5, an empty string was provided as value for DEFAULT clause of name attribute.

Figure 4: DESCRIBE Command in MySQL