SQL Constraints with Examples
Introduction to SQL Constraints
SQL constraints are like the rules that govern your database. They ensure that the data entered into a table adheres to predefined conditions, preventing the introduction of erroneous or inconsistent data. Constraints play a pivotal role in maintaining data accuracy and reliability.
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.
Types Of SQL Constraints
| Constraints | Description |
|---|---|
| Primary Key Constraint: | Enforces the uniqueness and non-null nature of a primary key column. It uniquely identifies each record in a table. |
| Unique Constraint: | Ensures that the values in a column are unique across the table. Unlike the primary key, multiple unique constraints can exist in a table. |
| Foreign Key Constraint: | Establishes relationships between tables by linking a column in one table to the primary key column in another table. |
| Check Constraint: | Enforces specific conditions on data entered into a column. It can be used to validate data against predefined criteria. |
| Not Null Constraint: | Ensures that a column cannot contain null values. It mandates that a column must always have a value. |
| Default Constraint: | Assigns a default value to a column when no explicit value is provided during insertion. |
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
