SQL NOT NULL Constraints with Examples
Introduction to SQL NOT NULL Constraints
In SQL, the “NOT NULL” constraint is used to ensure that a column in a database table always contains a value. It means that when you insert a new row into a table, the column with the “NOT NULL” constraint must have a value assigned to it, and this value cannot be left empty or NULL.
In this article, we will delve into the significance of NOT NULL constraints, understand their usage, and explore real-world examples to grasp their practical application.
Understanding the SQL Constraints
- SQL constraints are like the unsung heroes that silently enforce a set of predefined rules on the data stored in a database. These rules ensure that the data adheres to specific criteria, preventing the introduction of erroneous or inconsistent information.
- Constraints are essentially safeguards that help maintain the quality and reliability of data throughout its lifecycle.
not null constraint syntax
The syntax for applying the “NOT NULL” constraint to a column when creating a table in SQL:
CREATE TABLE table_name (
column1 datatype NOT NULL,
column2 datatype,
...
);
In this syntax:
- table_name: Replace this with the name you want to give to your table.
- column1: Specify the name of the first column to be created.
- datatype: Define the data type for column1. This could be INT, VARCHAR, DATE, or any other valid data type, depending on your requirements.
- NOT NULL: This is the constraint you add to indicate that the column1 should not allow NULL values.
Click below to access free SQL quizzes which will be helpful in your placement exams
Exploring SQL Constraints with Examples
Unique Constraint
The unique constraint ensures that the values in a specific column are distinct and do not repeat. This is particularly useful for columns that should contain only unique data, such as email addresses or usernames. Let’s consider an example using a “Users” table:
CREATE TABLE Users (
UserID INT PRIMARY KEY,
Username VARCHAR(50) UNIQUE,
Email VARCHAR(100) UNIQUE
);
In this example, the “username” and “email” columns are marked as unique, preventing any duplicate usernames or email addresses from being inserted into the table.
| User ID | Username | |
|---|---|---|
| 1 | john Doe | john@example.com |
| 2 | jane Smith | jane@example.com |
| 3 | bob Brown | bob@example.com |
Primary Key Constraint
The primary key constraint uniquely identifies each record in a table. It ensures that the key column values are unique and not null. Let’s create a “Products” table with a primary key:
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100) NOT NULL,
Price DECIMAL(10, 2)
);
Here, the “Product_ID” column serves as the primary key, guaranteeing that each student has a unique identifier.
| ProductID | Productname | Price |
|---|---|---|
| 1 | Laptop | 799.99 |
| 2 | Smartphone | 499.99 |
| 3 | Headphones | 79.99 |
Foreign Key Constraint
Foreign key constraints establish relationships between tables. They ensure referential integrity by linking a column in one table to the primary key column in another table. Consider the following “Orders” and “Customers” tables:
CREATE TABLE Customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100),
-- Other columns...
);
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
order_date DATE,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
-- Other columns...
);
Customers Table:
| CustomerID | CustomerName |
|---|---|
| 1 | John Doe |
| 2 | Jane Smith |
| 3 | Robert Johnson |
In this scenario, the “customer_id” column in the “Orders” table is a foreign key that references the “customer_id” column in the “Customers” table. This ensures that an order can only be associated with an existing customer.
Orders Table:
| OrderID | CustomerID | Orderdate |
|---|---|---|
| 101 | 1 | 2023-08-15 |
| 102 | 2 | 2023-08-16 |
| 103 | 3 | 2023-08-17 |
Check Constraint
The check constraint enforces specific conditions on data entered into a column. For example, let’s create a “Employees” table with a check constraint on the “Salary” column to ensure that only positive values are allowed:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Salary DECIMAL(10, 2) CHECK (Salary >= 0)
);
Here, the check constraint ensures that the “Salary” of a product is always non-negative.
| EmployeeID | FirstName | LastName | Salary |
|---|---|---|---|
| 1 | John | Doe | 50000.00 |
| 2 | Jane | Smith | 60000.00 |
| 3 | Bob | Brown | 55000.00 |
Not Null Constraint
The not null constraint ensures that a column cannot contain null values. It is often applied to columns that must always have a value. Let’s consider a “Addressess” table:
CREATE TABLE Addresses (
AddressID INT PRIMARY KEY,
StreetAddress VARCHAR(255) NOT NULL,
City VARCHAR(50) NOT NULL,
State VARCHAR(50),
PostalCode VARCHAR(10) NOT NULL
);
the “Addresses” table is designed to store address information, where each address is uniquely identified by an AddressID. It enforces data integrity by ensuring that essential fields like StreetAddress, City, and PostalCode are not missing, while allowing the State field to be optional by permitting NULL values.
| AddressID | StreetAddress | City | State | PostalCode |
|---|---|---|---|---|
| 1 | 123 Main St | Anytown | CA | 12345 |
| 2 | 456 Elm St | Somewhere | TX | 67890 |
| 3 | 789 Oak Ave | Nowhere | NY | 54321 |
Default Constraint
The default constraint assigns a default value to a column when no explicit value is provided during insertion. Let’s use the “Tasks” table as an example:
CREATE TABLE Tasks (
TaskID INT PRIMARY KEY,
TaskName VARCHAR(100) NOT NULL,
Status VARCHAR(20) DEFAULT 'Pending'
);
Here, the “start_date” column is assigned the current date as its default value if no date is specified during insertion.
| TaskID | Taskname | Status |
|---|---|---|
| 1 | Task 1 | Pending |
| 2 | Task 2 | Complete |
| 3 | Task 3 | Pending |
Conclusion
SQL constraints are the guardians of data integrity. By enforcing rules and conditions, they elevate the reliability and accuracy of your data, forming the backbone of a robust database structure. Whether it’s ensuring unique identifiers, maintaining relationships, or imposing specific conditions, SQL constraints play an indispensable role.
Prime Course Trailer
Related Banners
Get PrepInsta Prime & get Access to all 200+ courses offered by PrepInsta in One Subscription
Question 1.
What happens if I violate a primary key constraint?
Violating a primary key constraint will result in an error during insertion, preventing duplicate key values.
Question 2.Can I have multiple unique constraints in a single table?
Yes, a table can have multiple unique constraints, each ensuring the uniqueness of a different column.
Question 3.
Can I alter or remove constraints from an existing table?
Yes, but altering or removing constraints should be done cautiously, as it might lead to data inconsistencies.
Question 4. Are constraints only about data uniqueness?
No, constraints also encompass rules related to referential integrity, data validation, and default values.
Get over 200+ course One Subscription
Courses like AI/ML, Cloud Computing, Ethical Hacking, C, C++, Java, Python, DSA (All Languages), Competitive Coding (All Languages), TCS, Infosys, Wipro, Amazon, DBMS, SQL and others
