Default Constraint is defined by creating a default value for a column.

The default constraint provides a default value when no value is provide when inserting a new record in the table. Each column can have only one default constraint. Default constraint can be altered or removed.

For example, when inserting the record South Park Machinery, if we don't add a value for ContactName then the default value Unknown will be inserted automatically.

The example below illustrates how to add a DEFAULT constraint in Customers table.

1. Open MySQL Workbench (SQLyog, etc., or your favourite MySQL Client). In the database list, select northwind.
2. Click the plus sign next to northwind to view all its tables.
3. Click once on customers table
4. On the menu bar across the top, select Table -> Alter Table...
5. Type in the word Unknown in the column Default for ContactName.

Nullability Constraint

Nullability Constraint is defined by allowing NULL or NOT NULL values for a column.

It defines that when insert or update a column, null values (the absence of a value) are allowed. If NOT NULL is defined for a column, no null values can be saved in the column. In other words, a specific value which conforms to the data type defined for the column must be entered for each record. Note that NULL is not equal to empty or zero. Empty is a value of string type of data. Zero is a value of integer type of data.

In practice, I always try to avoid using ALLOW NULL in a column. To deal with the situation where users don't provide a value for a record in the column, I create a meaningful default value where possible such as NA or 0.

There are good reasons behind this practice of avoiding the use of NULLable columns.

1. First, null means unknown and that brings in uncertainty and so potential code bugs later in your development. For example, when writing SQL statements, we often don't pay attention to the existence of NULL values in a column and so don't add logic in SQL to deal with it such as using IS NULL to test data. This SQL would definitely return less data than it should.

2. Second, NULL values can't be indexed and so when we query a table for NULL values, a full table scan has to be performed. Table scan is resource intensive and will be very slow and so will dramatically increase query execution time if the table is very large.

3. Third, when we allow NULL values in foreign key columns, we have to use OUTER JOIN to retrieve both child rows that have a value for foreign key (pointing back to the primary key table) and those orphaned child rows who don't have a value for foreign key. OUTER JOIN is very slow and should be avoided whenever possible. On the other hand, if we only use INNER JOIN, those orphaned rows will not be retrieved in the query result. To get them separately, we need to use IS NULL and then use UNION statement to get the full result. As we have mentioned above, using IS NULL will make a full table scan which slows down query performance.

Sometimes when providing a default value is not practical, we have to use ALLOW NULL.

On next page, we are going to look at How to enforce Primary Key constraint and Unique constraint.

Other tutorials in this category

1. How to Design Relational Database

2. Enforce Data Integrity by Database Constraints

3. How to Enforce Data Type Constraint

4. Primary Key Constraint and Unique Constraint

5. Foreign Key Relationships and Considerations

Back to Tutorial Index Page