SQL Server NOT NULL CONSTRAINT

Learning Objective

The objective of this SQL Server tutorial is to teach you where and how to use the NOT NULL constraint in SQL and its significance.

What is NOT NULL CONSTRAINT in SQL Server?

The NULL value in SQL represents the absence of value. It is different from zero (0) or empty space (‘’). It simply means that there is no value for the field or column in the record. By default, a column can contain NULL values. So, to allow NULL values for a column we do not really have to do anything (though it is possible to explicitly specify the same using the NULL constraint). But if we do not want a column to have NULL value it has to be explicitly specified using the NOT NULL constraint.

The NOT NULL constraint is enforced on columns which contain additional or optional data which is not mandatory and hence can be skipped.

Operation

The NOT NULL constraint for a column can be specified at the time of table creation using the CREATE TABLE statement or later using the ALTER TABLE statement. The constraint can also be removed if not required at a later point in time using an ALTER TABLE statement.

SQL Server NOT NULL CONSTRAINT Syntax

The basic syntax of enabling the NOT NULL CONSTRAINT using CREATE TABLE and ALTER TABLE statements and removing using ALTER TABLE statement is given below.

CREATE TABLE Syntax

CREATE TABLE table_name (
column1 DATATYPE,
column2 DATATYPE NOT NULL,
column3 DATATYPE NOT NULL,
………………………………);

ALTER TABLE Syntax (to enforce constraint)

ALTER TABLE table_name
ALTER COLUMN column_name DATATYPE NOT NULL;

ALTER TABLE Syntax (to remove constraint)

ALTER TABLE tablename
ALTER COLUMN column_name DATATYPE;

In this syntax,

  • CREATE – clause used to create a database object (like table, index, schema etc.).
  • DATATYPE – keyword identifying the type of the data i.e. whether int, string, date etc.
  • ALTER – clause used to modify a database object (like adding column to a table).
  • COLUMN –clause used to tell SQL that the alteration is for a column.
  • NOT NULL – keyword combination to tell SQL that the column cannot have NULL values.
  • NULL – keyword to tell SQL that the column can have NULL values.

SQL Server NOT NULL CONSTRAINT Examples

Suppose we need to create a table to hold customer information. There is some mandatory information like customer name, sex, age, address, phone number and some optional information like email, income, marital status. We can create a table enforcing the NOT NULL constraint for the columns that will hold the mandatory information with the following create table query.

CREATE TABLE customers
(id INT IDENTITY(1,1) NOT NULL,
name VARCHAR(50) NOT NULL,
sex char(1) NOT NULL,
age tinyint NOT NULL,
address varchar(255) NOT NULL,
phone varchar(15) NOT NULL,
email varchar(50),
income decimal(10,2),
married BIT);

Now we will check by running 2 queries – in the first query we respect the NOT NULL constraints and in the second query we violate the NOT NULL constraints for age, address and phone without specifying any values for them. We can see from below that the first query runs successfully and the record is added and the second query fails citing NULL constraint failure. So only one record is inserted.

INSERT INTO customers
(name, sex, age, address, phone, email, income, married)
VALUES
('Nancy Poe', 'F', 28, 'Pennsylvania, USA', '+16117715919', 'npn_779@gmail.com', 45000.00, 0);
insert into customers
(name, sex, email, income, married)
values
('Chris Bale', 'M', 'new_cb_119@gmail.com', 60000.00, 1);

Msg 515, Level 16, State 2, Server MY-HP245G6, Line 1
Cannot insert the value NULL into column 'age', table 'master.dbo.customers'; column does not allow nulls. INSERT fails.
The statement has been terminated.

The table looks like below with one record in it.

idnamesexageaddressphoneemailincomemarried
1Nancy PoeF28Pennsylvania, USA16117715919npn_779@gmail.com450000

Now we will remove the NOT NULL constraints for age, address and phone columns one by one using the ALTER TABLE statements as below. Note that I have not specified NULL against the columns as NULL is default and explicit specification is not mandatory.

ALTER TABLE customers ALTER COLUMN age tinyint;
ALTER TABLE customers ALTER COLUMN address varchar(255);
ALTER TABLE customers ALTER COLUMN phone varchar(15);

Now we can run the following insert query which had failed previously and it will run successfully.

insert into customers
(name, sex, email, income, married)
values
('Chris Bale', 'M', 'new_cb_119@gmail.com', 60000.00, 1);

After insert the table will look like below with 2 records in it.

idnamesexageaddressphoneemailincomemarried
1Nancy PoeF28Pennsylvania, USA16117715919npn_779@gmail.com450000
6Chris BaleMNULLNULLNULLnew_cb_119@gmail.com600001

Now we will add back the constraints again and restore the table to its original state using the ALTER TABLE statements as below.

ALTER TABLE customers ALTER COLUMN age tinyint NOT NULL;
ALTER TABLE customers ALTER COLUMN address varchar(255) NOT NULL;
ALTER TABLE customers ALTER COLUMN phone varchar(15) NOT NULL;

Advertisement