MySQL NOT NULL Constraint

MySQL NOT NULL Constraint

The NOT NULL constraint in MySQL ensures that a column cannot have a NULL value. It is used when a column must always contain a value, making it impossible to insert or update a row without providing a valid value for the column.

Key Features

1. Prevents NULL Values: Ensures that every row in the column has a value.
2. Default Behavior: If a value is not explicitly provided during an INSERT or UPDATE, the operation will fail unless a default value is defined.
3. Applies at Table Creation or Modification: This can be added when creating a table or later using the ALTER TABLE statement.

1. Defining NOT NULL During Table Creation

Syntax

CREATE TABLE table_name (
    column_name data_type NOT NULL
);

Example

Create a users table where the name and email columns cannot be NULL:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    age INT
);

2. Adding NOT NULL to an Existing Column

You can modify an existing column to enforce the NOT NULL constraint.

Syntax

ALTER TABLE table_name
MODIFY column_name data_type NOT NULL;

Example

Add NOT NULL to the age column in the users table:

ALTER TABLE users
MODIFY age INT NOT NULL;

3. Removing NOT NULL from a Column

If necessary, you can remove the NOT NULL constraint using the ALTER TABLE statement.

Syntax

ALTER TABLE table_name
MODIFY column_name data_type NULL;

Example

Remove the NOT NULL constraint from the age column:

ALTER TABLE users
MODIFY age INT NULL;

4. Using Default Values with NOT NULL

To avoid errors when inserting data, you can set a default value for a column with the NOT NULL constraint.

Syntax

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

Example

Create a products table with a NOT NULL column that has a default value:

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL DEFAULT 0.00
);

5. Checking for NOT NULL Columns

You can verify which columns have the NOT NULL constraint by using the DESCRIBE or SHOW COLUMNS command.

Example

DESCRIBE users;

Result

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | INT         | NO   | PRI | NULL    | auto_increment |
| name  | VARCHAR(100)| NO   |     | NULL    |                |
| email | VARCHAR(100)| NO   |     | NULL    |                |
| age   | INT         | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

6. Examples of Insert/Update with NOT NULL

Insert Without Value

If you try to insert a row without a value for a NOT NULL column, MySQL will throw an error.

INSERT INTO users (email) VALUES ('example@gmail.com');

Error

ERROR 1364 (HY000): Field 'name' doesn't have a default value

Insert with Value

Provide value for all NOT NULL columns:

INSERT INTO users (name, email) VALUES ('John Doe', 'john.doe@example.com');

Update a NOT NULL Column

UPDATE users SET name = NULL WHERE id = 1;

Error

ERROR 1048 (23000): Column 'name' cannot be null

7. Best Practices

1. Use NOT NULL for Essential Data: Apply the constraint to columns that must always have meaningful values, such as name, email, or price.
2. Combine with Defaults: Provide default values to simplify data insertion.
3. Avoid Excessive Use: Use NOT NULL only where necessary to maintain database flexibility.

Let me know if you need further details or examples!