in sql, not null constraint in sql ensures a column must always contain a value and cannot be left empty. unlike a primary key, which uniquely identifies each record and also disallows nulls, not null only enforces the presence of data without requiring uniqueness.

not null is used to enforce mandatory fields.
it prevents null values from being inserted or updated.
it is applicable at the column level.
it can be used during table creation or modification (with the alter command).

syntax:

create table table_name
(
column1 data_type(size) not null,
column2 data_type(size) not null,
....
);

in the above syntax:

create table table_name: creates a new table.
column1 data_type(size): defines a column with its name, data type, and size.
not null: ensures the column cannot have null (empty) values.
repeat for other columns as needed.

sql not null constraint syntax

the syntax for applying the not null constraint can be as follows:

1. during table creation:

create table table_name (
    column1 data_type(size) not null,
    column2 data_type(size) not null,
    ...
);

2. modifying an existing table:

you can also add a not null constraint to an existing column in a table using the alter table statement:

alter table table_name
modify column_name data_type(size) not null;

example 1: applying not null in table creation

let’s look at an example where we create an employees table with a not null constraint applied to the empid column to ensure that each employee has a unique, non-null id.

query:

create table emp (
    empid int not null primary key,
    name varchar(50),
    country varchar(50),
    age int(2),
    salary int(10)
);

in this table:

empid is a mandatory field, ensuring no employee record can exist without an id.

example 2: adding not null to an existing table

if you have an existing table, you can add a not null constraint to a column using the alter table statement. let’s assume the emp table already exists, and we now want to make the name column non-nullable.

query:

alter table emp 
modify name varchar(50) not null;

now, the name column will not accept null values, meaning every employee record must have a name.

real-world use case of not null

consider an orders table in an e-commerce database. for each order, you would likely have mandatory fields such as orderid, customerid, productid, and orderdate. these fields should never be null, as they represent essential information for tracking and processing orders.

example orders table:

create table orders (
    orderid int not null primary key,
    customerid int not null,
    productid int not null,
    orderdate date not null
);

this table ensures that:

every order has a unique orderid.
customerid, productid, and orderdate must always be provided.

suggested quiz

5 questions

what does the not null constraint ensure in a column?

a

column must stay unique always
b

column must not store null ever
c

column auto-generates values only
d

column becomes primary key always

explanation:

not null simply prevents storing null values; it does not enforce uniqueness or act as a primary key.

how is not null different from a primary key?

a

both enforce uniqueness only
b

both forbid null values only
c

only not null creates key fields
d

only primary key enforces unique

explanation:

primary key enforces both uniqueness and no nulls, while not null enforces only non-empty values.

which command is used to add not null to an existing column?

a

update table … set not null
b

alter table … modify not null
c

change column … add not null
d

create table … with not null

explanation:

the alter table … modify syntax is used to apply not null to an existing column.

when is not null usually applied?

a

when a field must always hold data
b

when a field must store duplicates
c

when a field must depend on joins
d

when a field must remain optional

explanation:

not null enforces mandatory fields that must always contain values during inserts or updates.

what happens if you try inserting null into a not null column?

a

sql silently ignores the error
b

sql auto-fills with default values
c

sql throws an error preventing save
d

sql converts null to zero internally

explanation:

the not null rule blocks inserts or updates that attempt to store null in restricted columns.

quiz completed successfully

your score : 2/5

accuracy : 0%

1/5 1/5 previous next >

Save or share this article