Anatomy of Not NULL and Default Keywords

Today we will look into two different keywords altogether, NOT NULL and DEFAULT.

NOT NULL : Before finding out the use of NOT NULL constraint, let us just execute a simple select query to retrieve myAddressBook Table first.

SELECT * from myaddressbook;

Take a look at the above content. Here in the table we see couple of null entries. What if your address book doesn't have the contact person name or email id? With out having proper contact information it will be very difficult to communicate right?.


Similarly certain information cannot be just ignored or cannot be kept empty. In order to provide such mandate information/values, while creating a table all we have to do is add NOT NULL keyword right after the data type.
You need to be selective on adding NOT NULL and choose appropriate columns for which you need to have values in your table.Well in our example, myAddressBook table structure, I don’t want any of the contact information to be empty hence myaddressbook can be re-written as shown below.

CREATE TABLE myaddressbook
(firstName VARCHAR(30) NOT NULL, lastName VARCHAR(20) NOT NULL, status VARCHAR(20) NOT NULL, gender CHAR(1) NOT NULL, location VARCHAR(50) NOT NULL, birthday DATE NOT NULL, email VARCHAR(50) NOT NULL);

Note: If you are already having myaddressbook Table just drop it. There is also an another way of modifying, without dropping the table using ALTER keyword. We shall look into it sometime later.

Let us just view the newly created table structure

Now if you notice there is a word NO under NULL, which clearly says that the column cannot have NULL values.

DEFAULT

Using DEFAULT key word we can fill empty columns with a specific value.
Lets create a simple mystore table to demonstrate the same.

CREATE TABLE mystore
( statname VARCHAR (20) NOT NULL,
price FLOAT (3,2) NOT NULL default 1.00);

In the above query we have specified the default value as 1.00. Now if we don’t provide any price value while inserting data into mystore table, it should take the default value. Let us insert some values into mystore and execute the SELECT query to view the table as shown below.

First time when we inserted data we only provided value to statname column, though price column was defined to be NOT NULL still the query was executed successfully. Its only because of the default keyword as we didn't provide any value while inserting, the default specified value got inserted in the first row.

Technorati Tags: default keyword in SQL,NOT NULL,default,mysql,sql,NOT NULL in mysql,default in mysql

del.icio.us Tags: default keyword in SQL,NOT NULL,default,mysql,sql,NOT NULL in mysql,default in mysql