NOT NULL, PRIMARY KEY, and NULL Values

Most aggregate functions ignore NULL inputs, but they can return NULL when every input is NULL; COUNT(*) counts rows. Empty-string behaviour also differs in some databases.

1. NOT NULL Constraint

Definition

The NOT NULL constraint ensures that a column must always contain a value.

It does not allow NULL values.

Use NOT NULL when a field is mandatory and should always be present.

Examples:

• customer name
• email
• order date
• quantity

1.1 Creating a Table with NOT NULL

DROP TABLE orders;

CREATE TABLE orders (
  order_id INT NOT NULL,
  order_item_id INT NOT NULL,
  order_date DATE NOT NULL,
  customer_id INT NOT NULL,
  order_status VARCHAR(20) NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  product_price FLOAT NOT NULL,
  total_price FLOAT NOT NULL
);

Explanation

Every column must contain a value.

If any column receives NULL, SQL will reject the insert.

1.2 Example: Failing INSERT Because of NOT NULL

INSERT INTO orders VALUES
(1, 1, '2013-07-25', 11599, NULL, 957, 1, 299.98, 299.98);

This fails because:

order_status = NULL

But order_status is defined as NOT NULL.

1.3 Another Example (customers table)

CREATE TABLE customers (
  customer_id INT NOT NULL,
  customer_fname VARCHAR(45) NOT NULL,
  customer_lname VARCHAR(45) NOT NULL,
  customer_email VARCHAR(45) NOT NULL,
  customer_phone VARCHAR(45),
  customer_street VARCHAR(255),
  customer_city VARCHAR(45) NOT NULL,
  customer_state VARCHAR(45) NOT NULL,
  customer_zipcode VARCHAR(10)
);

Explanation

Required fields:

• customer_id
• first name
• last name
• email
• city
• state

Optional fields:

• phone
• street
• zipcode

Optional columns can store NULL.

2. PRIMARY KEY Constraint

Definition

A PRIMARY KEY is a column (or combination of columns) that uniquely identifies each row in a table.

A primary key must:

1. Be unique
2. Never be NULL
3. Identify exactly one row

Each table can have only one primary key, but that key can contain multiple columns.

2.1 Why Do We Need a Primary Key?

Without a primary key:

• duplicate rows are possible
• records cannot be uniquely identified
• updates and deletes become risky

Primary keys solve this by giving each row a unique identity.

2.2 Duplicate Data Without a Primary Key

CREATE TABLE customers (
  customer_id INT,
  customer_fname VARCHAR(45),
  customer_lname VARCHAR(45),
  customer_email VARCHAR(45),
  customer_phone VARCHAR(45),
  customer_street VARCHAR(255),
  customer_city VARCHAR(45),
  customer_state VARCHAR(45),
  customer_zipcode VARCHAR(10)
);

INSERT INTO customers VALUES
(1,'Richard','Hernandez','richard@gmail.com',NULL,'6303 Plaza','Brownsville','TX','78521');

INSERT INTO customers VALUES
(1,'Mary','Barrett','mary@gmail.com',NULL,'Embers Ridge','Littleton','CO','80126');

Explanation

Both rows use:

customer_id = 1

This is allowed because no primary key exists.

That means duplicates are possible.

2.3 PRIMARY KEY at Column Level

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  customer_fname VARCHAR(45),
  customer_lname VARCHAR(45),
  customer_email VARCHAR(45),
  customer_phone VARCHAR(45),
  customer_street VARCHAR(255),
  customer_city VARCHAR(45),
  customer_state VARCHAR(45),
  customer_zipcode VARCHAR(10)
);

Explanation

The primary key is defined directly beside the column.

2.4 PRIMARY KEY at Table Level

CREATE TABLE customers (
  customer_id INT,
  customer_fname VARCHAR(45),
  customer_lname VARCHAR(45),
  customer_email VARCHAR(45),
  customer_phone VARCHAR(45),
  customer_street VARCHAR(255),
  customer_city VARCHAR(45),
  customer_state VARCHAR(45),
  customer_zipcode VARCHAR(10),
  PRIMARY KEY (customer_id)
);

Explanation

This works the same as column-level definition.

Used more often when defining composite keys.

2.5 Invalid: Two Separate Primary Keys

CREATE TABLE order_items (
  order_id INT PRIMARY KEY,
  product_id INT PRIMARY KEY,
  quantity INT
);

Explanation

This is invalid.

A table can have only one primary key.

2.6 Composite Primary Key

CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  quantity INT,
  PRIMARY KEY (order_id, product_id)
);

Explanation

The combination of:

• order_id
• product_id

must be unique.

This is called a Composite Primary Key.

Used when one column alone is not enough to uniquely identify a row.

2.7 Composite Key Example

INSERT INTO order_items VALUES (1,101,2);
INSERT INTO order_items VALUES (1,102,3);
INSERT INTO order_items VALUES (1,102,5);

The third insert fails because:

(1,102)

already exists.

3. NULL vs 0 vs Empty String

This is one of the most important concepts in SQL.

These 3 values look similar, but they mean very different things.

3.1 What is NULL?

NULL means:

• no value
• unknown value
• missing data
• not applicable

It does not mean zero.

It does not mean blank text.

It means: there is no value stored here.

Example: Middle Name

Explanation

Rohan has no middle name recorded.

That means:

middle_name = NULL

This means the value is unknown or not applicable.

3.2 What is 0?

0 is a valid number.

It means:

• the value exists
• the value is known
• the value is exactly zero

Example: Orders

Explanation

For order 102:

quantity = 0

This means quantity is known and is exactly zero.

For order 103:

quantity = NULL

This means quantity is unknown.

3.3 What is an Empty String (’’)?

'' means:

• value exists
• it is text
• text length is zero

It is not NULL.

It means: an empty piece of text.

Example: Nickname

Explanation

• '' → user intentionally kept nickname blank
• NULL → nickname was never filled

These are different.

4. How They Affect Queries

4.1 Filtering NULL vs Empty String

SELECT * FROM inventory WHERE notes = '';

Finds only rows with empty string.

SELECT * FROM inventory WHERE notes IS NULL;

Finds only rows with NULL.

These are not the same.

4.2 NULL in Aggregations

SELECT AVG(stock_level) FROM inventory;

SQL ignores NULL values in aggregate functions.

Example values:

• 10
• 0
• NULL
• 5

Average becomes:

(10 + 0 + 5) / 3

Not divided by 4.

Important Rule

• NULL is ignored
• 0 is included

This changes reports significantly.

4.3 COUNT(*) vs COUNT(column)

COUNT(*)

Counts all rows.

COUNT(column_name)

Counts only non-NULL values.

This is very important in analytics.

5. Summary

NOT NULL

• Column must always contain a value
• NULL is not allowed
• Used for mandatory fields

PRIMARY KEY

• Uniquely identifies each row
• Cannot be NULL
• Cannot be duplicated
• One per table
• Can be composite

NULL

• Missing / unknown value
• Not zero
• Not blank
• Ignored in AVG(), SUM(), COUNT(column)

0

• Valid numeric value
• Means known zero
• Included in math

Empty String (’’)

• Valid text value
• Length is zero
• Not NULL