You are currently viewing SQL 1.47 NOT NULL Constraint

SQL 1.47 NOT NULL Constraint

NOT NULL Constraint


In the realm of SQL (Structured Query Language), constraints play a crucial role in maintaining the integrity of your database. One such constraint is the NOT NULL constraint, which ensures that a column cannot contain any NULL values. In this tutorial, we’ll delve into the fundamentals of the NOT NULL constraint, its syntax, usage, and scenarios where it proves beneficial.


The NOT NULL constraint is a column constraint that ensures a column does not accept NULL values. When you define a column with the NOT NULL constraint, it means that every row in that column must contain a value.

Syntax


The syntax for defining a column with the NOT NULL constraint in SQL is as follows:

CREATE TABLE table_name (
    column_name data_type NOT NULL
);

Sample Scenario


Let’s create a simple table named Employees with columns EmployeeID, FirstName, and LastName. We’ll enforce the NOT NULL constraint on the FirstName and LastName columns.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL
);
  1. Explanation:
  • CREATE TABLE: This SQL statement is used to create a new table.
  • Employees: This is the name of the table we’re creating.
  • (EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL): This part defines the table’s schema. We have three columns: EmployeeID (an integer type and the primary key), FirstName, and LastName. Both FirstName and LastName have the NOT NULL constraint applied, meaning they must contain a value for each row.

Inserting Data


Now, let’s insert some data into the Employees table:

INSERT INTO Employees (EmployeeID, FirstName, LastName) VALUES
(1, 'John', 'Doe'),
(2, 'Jane', 'Smith'),
(3, 'Alice', 'Johnson'),
(4, 'Bob', 'Brown');

Checking Constraint Violation


Let’s try to insert a row with a NULL value in the FirstName column:

INSERT INTO Employees (EmployeeID, FirstName, LastName) VALUES
(5, NULL, 'Invalid');
Expected Output:


You should receive an error message indicating a constraint violation:

ERROR:  null value in column "FirstName" violates not-null constraint

Conclusion


In this tutorial, we’ve explored the NOT NULL constraint in SQL. We learned its syntax, usage, and how it ensures data integrity by disallowing NULL values in specified columns. Integrating NOT NULL constraints into your database design helps maintain consistency and reliability in your data.

Leave a Reply