SQL NOT NULL Constraint

In SQL, the NOT NULL constraint is used to ensure that a column in a table cannot have a NULL value. When you define a column with this constraint, it means that every record must include a value for this column; it cannot be left empty.

Here’s a basic example to illustrate how it works:

Example: Creating a Table with NOT NULL Constraint

Suppose we want to create a table called Employees with the following columns:

• EmployeeID (integer, cannot be NULL)
• FirstName (text, cannot be NULL)
• LastName (text, can be NULL)
• HireDate (date, cannot be NULL)

Here’s how you would write the SQL statement to create this table:

CREATE TABLE Employees (
    EmployeeID INT NOT NULL,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50),
    HireDate DATE NOT NULL,
    PRIMARY KEY (EmployeeID)
);

Inserting Data into the Table

Now, let's insert some data into this table:

INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate) 
VALUES (1, 'John', 'Doe', '2024-07-25');

INSERT INTO Employees (EmployeeID, FirstName, HireDate) 
VALUES (2, 'Jane', 'Smith', '2024-07-26');  -- This will fail because LastName is NULL and it's allowed

Output

If you run the above insert statements, the first one will succeed because it follows the constraints. The second one will fail if LastName is specified as NOT NULL in the table definition (if not, it will succeed with a NULL value in LastName).

Error Example

If you try to insert a row where a NOT NULL column is left out, you will get an error. For example:

INSERT INTO Employees (EmployeeID, HireDate) 
VALUES (3, '2024-07-27');

This will fail with an error like:

ERROR: column "FirstName" contains null values

This indicates that the FirstName column cannot be NULL, and thus the insert operation cannot proceed.

In summary, the NOT NULL constraint is crucial for ensuring that critical data fields are always populated, maintaining data integrity within your database.