NOT NULL and DEFAULT constraints in MySQL

Introduction

NOT NULL makes sure a column always has a value. DEFAULT sets a value automatically if none is given.

When you want to make sure a user's email is always stored.

When you want a new product's stock to start at zero if not specified.

When you want to avoid empty fields that could cause errors later.

When you want to give a default status like 'active' to new accounts.

When you want to ensure important data is never missing.

Syntax

MySQL

CREATE TABLE table_name (
  column_name datatype NOT NULL DEFAULT default_value
);

NOT NULL means the column cannot be left empty when inserting data.

DEFAULT sets a value automatically if you don't provide one during insert.

Examples

All columns must have a value; none can be empty.

MySQL

CREATE TABLE users (
  id INT NOT NULL,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL
);

stock will be 0 if no value is given when adding a product.

MySQL

CREATE TABLE products (
  id INT NOT NULL,
  name VARCHAR(50) NOT NULL,
  stock INT NOT NULL DEFAULT 0
);

status defaults to 'active' if not specified.

MySQL

CREATE TABLE accounts (
  id INT NOT NULL,
  username VARCHAR(50) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'active'
);

Sample Program

This creates a table where department defaults to 'General' if not given. We add two employees, one without specifying department.

MySQL

CREATE TABLE employees (
  id INT NOT NULL,
  name VARCHAR(50) NOT NULL,
  department VARCHAR(50) NOT NULL DEFAULT 'General'
);

INSERT INTO employees (id, name) VALUES (1, 'Alice');
INSERT INTO employees (id, name, department) VALUES (2, 'Bob', 'Sales');

SELECT * FROM employees;

Important Notes

If you try to insert NULL into a NOT NULL column, the database will give an error.

DEFAULT values help keep your data consistent and avoid missing information.

You can combine NOT NULL and DEFAULT to make sure a column always has a useful value.

Summary

NOT NULL means a column must have a value; it cannot be empty.

DEFAULT sets a value automatically if none is provided during insert.

Using both helps keep your data complete and consistent.