SQL NOT NULL Constraint

SQL NOT NULL Constraint

The SQL NOT NULL constraint is used to ensure that a column cannot have a NULL value. This command is essential for maintaining data integrity by ensuring that critical fields in a table always contain valid data.

---

Syntax

-- To create a NOT NULL constraint when creating a table
CREATE TABLE table_name (
    column1 datatype NOT NULL,
    column2 datatype,
    ...
);

-- To add a NOT NULL constraint to an existing column
ALTER TABLE table_name
MODIFY COLUMN column1 datatype NOT NULL;

• CREATE TABLE: This is the SQL keyword used to create a new table.
• NOT NULL: This constraint ensures that the column cannot have a NULL value.
• ALTER TABLE: This is the SQL keyword used to modify an existing table.
• MODIFY COLUMN: This command is used to change the definition of an existing column.
• column1: This specifies the name of the column to set as NOT NULL.
• datatype: This specifies the type of data the column can hold, such as INTEGER, VARCHAR, DATE, etc.
• table_name: This specifies the name of the table to create or modify.

---

Example

Let's go through a complete example that includes creating a database, creating a table with a NOT NULL constraint, and inserting data into the table.

Step 1: Creating a Database

This step involves creating a new database named example_db.

CREATE DATABASE example_db;

In this example, we create a database named example_db.

Step 2: Creating a Table with a NOT NULL Constraint

In this step, we create a table named employees within the previously created database, defining the first_name and last_name columns as NOT NULL.

USE example_db;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    hire_date DATE
);

Here, we define the employees table with columns for id, first_name, last_name, email, and hire_date. The first_name and last_name columns are set as NOT NULL.

Step 3: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('John', 'Doe', 'john.doe@example.com', '2023-01-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Jane', 'Smith', 'jane.smith@example.com', '2023-02-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Alice', 'Johnson', 'alice.johnson@example.com', '2023-03-01');
INSERT INTO employees (first_name, email, hire_date) VALUES (NULL, 'Brown', 'bob.brown@example.com', '2023-04-01');

The first three insertions will be successful because the first_name and last_name values are provided. The fourth insertion will fail because the first_name value is NULL, violating the NOT NULL constraint.

Step 4: Adding a NOT NULL Constraint to an Existing Column

This step involves adding the NOT NULL constraint to an existing column named email in the employees table.

ALTER TABLE employees
MODIFY COLUMN email VARCHAR(100) NOT NULL;

This command adds the NOT NULL constraint to the email column of the employees table, ensuring that the email value cannot be NULL.