SQL (Structured Query Language) is a programming language used to manage and manipulate relational databases. One of the most important aspects of SQL is the ability to define constraints on the data stored in a database. One such constraint is the NOT NULL constraint.
The NOT NULL constraint is used to ensure that a column in a table does not contain any NULL values. A NULL value represents a missing or unknown value and can cause problems when performing calculations or comparisons. By using the NOT NULL constraint, we can ensure that the data in our database is complete and accurate.
When a column is defined as NOT NULL, it means that every row in the table must have a value for that column. If a row is inserted into the table without a value for the NOT NULL column, an error will be thrown and the insertion will fail.
Let's take a look at some examples of how to use the NOT NULL constraint in SQL.
To create a table with a NOT NULL column, we can use the following syntax:
CREATE TABLE employees (
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
age INT
);
In this example, we have created a table called "employees" with three columns: "id", "name", and "age". The "id" and "name" columns are defined as NOT NULL, which means that every row in the table must have a value for these columns.
When inserting data into a table with a NOT NULL column, we must provide a value for that column. If we do not provide a value, the insertion will fail.
INSERT INTO employees (id, name, age)
VALUES (1, 'John Doe', 30);
INSERT INTO employees (id, name, age)
VALUES (2, 'Jane Smith', NULL);
In the first example, we have inserted a row into the "employees" table with values for all three columns. In the second example, we have attempted to insert a row without a value for the "age" column. Since the "age" column is not defined as NOT NULL, the insertion will succeed.
When updating data in a table with a NOT NULL column, we must provide a value for that column. If we do not provide a value, the update will fail.
UPDATE employees
SET age = 35
WHERE id = 1;
UPDATE employees
SET age = NULL
WHERE id = 2;
In the first example, we have updated the "age" column for the row with an "id" of 1. Since the "age" column is not defined as NOT NULL, we can set it to NULL without any issues. In the second example, we have attempted to set the "age" column to NULL for the row with an "id" of 2. Since the "age" column is defined as NOT NULL, the update will fail.