Unlocking the Power of SQL: A Comprehensive Guide to Structured Query Language and Database Operations Part 4

Continuing the thread from my previous SQL article, I invite you to explore the previous phase of our discussion, which can be found at the following link:

Unlocking the Power of SQL: A Comprehensive Guide to Structured Query Language and Database…

Table of content

SQL Create Constraints

1. NOT NULL
2. PRIMARY KEY
3. FOREIGN KEY
4. DEFAULT
5. UNIQUE
6. CHECK

SQL Create Constraints

Think of SQL constraints as rules you set for your table’s data to make sure it behaves nicely. You can set these rules in two ways:

Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement.

Syntax

CREATE TABLE table_name (
 column1 datatype constraint,
 column2 datatype constraint,
 column3 datatype constraint,
 ….
);

After the Table is Built: Alternatively, you can set rules even after your puzzle is complete, like deciding some new rules after you’ve made the puzzle.

ALTER TABLE table_name
ADD CONSTRAINT constraint_type (column_name);

Why Constraints Are Important:

1. Data Accuracy: Constraints make sure that the data going into your table is correct and reliable.
2. Preventing Mistakes: They prevent mistakes. If something doesn’t follow the rules, the action is stopped before it messes things up.

Types of Constraints:

1. NOT NULL: Think of this as saying, “Hey, this spot can’t be empty!” It ensures a column always has a value.
2. UNIQUE: Imagine everyone in a group having a different name. It ensures that all values in a column are different from each other.
3. PRIMARY KEY: This is like a special ID card for each row. It’s a combination of NOT NULL and UNIQUE. Each row gets a unique ID.
4. FOREIGN KEY: Think of this as a strong connection between two tables. It stops you from doing things that would break these connections.
5. CHECK: It’s like having a rulebook. You can set specific rules that the values in a column must follow.
6. DEFAULT: This is like having a default plan. If you don’t provide a value, the column gets a default value.
7. CREATE INDEX: Think of this like an index in a book. It helps you find things really fast on your table.

Where Constraints Apply:

• Column-Level Constraints: These rules apply to a specific column, like “This column can’t be empty” or “Each value must be unique.”
• Table-Level Constraints: These rules are for the whole table. They might involve multiple columns or rows. Like making sure all the pieces fit together just right.

The SQL NOT NULL constraint

is a rule that is applied to a column in a database table to ensure that the column does not contain any NULL values. This constraint guarantees that the column will always have a value, preventing the insertion or update of records without providing a value for that column.

Let’s break down the explanation and provide examples from easy to hard:

By default, a column in a database table can hold NULL values, which means it can be empty. The NOT NULL constraint makes sure that a column cannot have NULL values, requiring it to always contain some data.

Example 1:

Imagine you have a table named “Students” with columns “ID,” “Name,” and “Age.” Applying the NOT NULL constraint to the “Name” column means that every student record must have a name, and you cannot leave the name field empty when adding or updating a record.

Example 2

Continuing with the “Students” table, let’s use SQL to create the table with the NOT NULL constraint on multiple columns:

CREATE TABLE Students (
  ID int NOT NULL,
  Name varchar(255) NOT NULL,
  Age int NOT NULL
);

In this example, both the “ID” and “Age” columns are also constrained to NOT accept NULL values. This ensures that every student must have an ID, a name, and an age when added to the table.

Example 3

Now, let’s consider a more complex scenario with a table called “Orders” that tracks customer orders. We want to ensure that both the “CustomerID” and “ProductID” columns cannot be left empty:

CREATE TABLE Orders (
  OrderID int NOT NULL,
  CustomerID int NOT NULL,
  ProductID int NOT NULL,
  Quantity int
);

Here, the NOT NULL constraint is applied to the “OrderID,” “CustomerID,” and “ProductID” columns, making it mandatory to provide values for these columns when inserting new orders.

Example 4

Now, let’s say we have a table “Employees” that track employee information. We initially create the table without the NOT NULL constraint on the “Salary” column, but we later decide to enforce this constraint:

-- Create the table without NOT NULL constraint
CREATE TABLE Employees (
  EmployeeID int NOT NULL,
  LastName varchar(255) NOT NULL,
  FirstName varchar(255) NOT NULL,
  Salary decimal(10, 2)
);

-- Add NOT NULL constraint to Salary column using ALTER TABLE
ALTER TABLE Employees
ALTER COLUMN Salary decimal(10, 2) NOT NULL;

In this example, we first create the “Employees” table with the “Salary” column allowing NULL values. Then, we use the ALTER TABLE statement to modify the column and add the NOT NULL constraint, ensuring that employee records must have a specified salary value.

SQL UNIQUE Constraint:

The SQL UNIQUE constraint ensures that values in a column (or a set of columns) are unique across all rows in a table. This constraint guarantees that no two rows in the table can have the same values in the specified column(s), preventing duplicate entries.

Simple Explanation:

The UNIQUE constraint ensures that a specific column (or columns) only contains unique values, preventing the same value from being repeated in different rows.

Example 1:

Consider a table named “Students” with a column “StudentID.” By applying the UNIQUE constraint to the “StudentID” column, you ensure that each student is assigned a unique ID:

CREATE TABLE Students (
  StudentID int UNIQUE,
  FirstName varchar(255),
  LastName varchar(255)
);

• In this example, the UNIQUE constraint prevents multiple students from having the same StudentID.

Example 2:

Continuing with the “Students” table, let’s apply the UNIQUE constraint to a combination of columns to ensure that each student’s email address is unique:

CREATE TABLE Students (
  StudentID int,
  FirstName varchar(255),
  LastName varchar(255),
  Email varchar(255) UNIQUE
);

• Here, the UNIQUE constraint on the “Email” column prevents multiple students from having the same email address.

Example 3:

Now, consider a table “Books” where you want to ensure that each book has a unique ISBN (International Standard Book Number):

CREATE TABLE Books (
  BookID int,
  Title varchar(255),
  ISBN varchar(13) UNIQUE,
  Author varchar(255)
);

• The UNIQUE constraint on the “ISBN” column ensures that each book is uniquely identified by its ISBN.

Example 4:

In a more complex scenario, let’s create a table “Employees” where you want to ensure that both the combination of “DepartmentID” and “EmployeeID” is unique:

CREATE TABLE Employees (
  EmployeeID int,
  FirstName varchar(255),
  LastName varchar(255),
  DepartmentID int,
  UNIQUE (DepartmentID, EmployeeID)
);

• Here, the UNIQUE constraint on the combination of “DepartmentID” and “EmployeeID” guarantees that no two employees can have the same combination of department and employee IDs.

SQL PRIMARY KEY Constraint:

• The SQL PRIMARY KEY constraint is used to uniquely identify each row in a table. It enforces the uniqueness and non-null property of a column (or a set of columns) and automatically creates a unique index for fast data retrieval.
• You can think about it like the leader of the table. Because it can’t be repeated (can’t be more than one leader), can’t have null value (Each group should have a leader)

Simple Explanation:

The PRIMARY KEY constraint ensures that a specific column (or columns) contains unique values and cannot have NULL values. It uniquely identifies each row in the table.

Example 1:

Consider a table named “Students” where each student has a unique student ID:

CREATE TABLE Students (
  StudentID int PRIMARY KEY,
  FirstName varchar(255),
  LastName varchar(255)
);

In this example, the PRIMARY KEY constraint is applied to the “StudentID” column, ensuring each student is uniquely identified.

Example 2:

Continuing with the “Students” table, let’s apply a composite PRIMARY KEY using both “DepartmentID” and “StudentID” to uniquely identify students within each department:

CREATE TABLE Students (
  StudentID int,
  FirstName varchar(255),
  LastName varchar(255),
  DepartmentID int,
  PRIMARY KEY (DepartmentID, StudentID)
);

Here, the composite PRIMARY KEY ensures that each student is uniquely identified within their respective department.

Example 3:

Consider a table “Books” where you want to use an ISBN as the unique identifier for each book:

CREATE TABLE Books (
  BookID int,
  Title varchar(255),
  ISBN varchar(13) PRIMARY KEY,
  Author varchar(255)
);

In this example, the ISBN column is designated as the primary key, ensuring each book is uniquely identified by its ISBN.

Example 4:

In a more complex scenario, let’s create a table “Employees” where you want to use an automatically generated EmployeeID as the primary key:

CREATE TABLE Employees (
  EmployeeID int PRIMARY KEY AUTO_INCREMENT,
  FirstName varchar(255),
  LastName varchar(255),
  DepartmentID int
);

Here, the PRIMARY KEY constraint is applied to the “EmployeeID” column, and the AUTO_INCREMENT attribute ensures that each new employee is assigned a unique ID automatically

SQL PRIMARY KEY on ALTER TABLE

To create a PRIMARY KEY constraint on the "ID" column when the table is already created, use the following SQL:

ALTER TABLE Persons
ADD PRIMARY KEY (ID);

To allow the naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);

DROP a PRIMARY KEY Constraint

To drop a PRIMARY KEY constraint, use the following SQL:

MySQL:

ALTER TABLE Persons
DROP PRIMARY KEY;

SQL FOREIGN KEY Constraint

The SQL FOREIGN KEY constraint establishes a link between two tables, ensuring that the values in a column (or a set of columns) in one table match the values of a primary key in another table. This constraint maintains referential integrity and helps enforce relationships between tables.

Simple Explanation:

The FOREIGN KEY constraint guarantees that the values in a column of one table match the values in another table’s primary key, creating a connection between related data.

Example 1:

Consider two tables, “Students” and “Courses,” where each student is enrolled in a course. We use the FOREIGN KEY constraint to connect the “StudentID” column in the “Students” table with the “StudentID” column in the “Courses” table:

CREATE TABLE Students (
  StudentID int PRIMARY KEY,
  FirstName varchar(255),
  LastName varchar(255)
);

CREATE TABLE Courses (
  CourseID int PRIMARY KEY,
  CourseName varchar(255),
  StudentID int,
  FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);

In this example, the FOREIGN KEY constraint on the “Courses” table references the “StudentID” column in the “Students” table, establishing a relationship between students and their enrolled courses.

Example 2:

Continuing with the “Students” and “Courses” tables, let’s enhance the relationship with cascading actions. When a student is deleted, their enrolled courses should also be removed:

CREATE TABLE Students (
  StudentID int PRIMARY KEY,
  FirstName varchar(255),
  LastName varchar(255)
);

CREATE TABLE Courses (
  CourseID int PRIMARY KEY,
  CourseName varchar(255),
  StudentID int,
  FOREIGN KEY (StudentID) REFERENCES Students(StudentID) ON DELETE CASCADE
);

The “ON DELETE CASCADE” option ensures that when a student is deleted, the associated course records are automatically removed as well.

Example 3:

Consider tables “Employees” and “Departments,” where each employee is assigned to a department. We establish a relationship using the FOREIGN KEY constraint:

CREATE TABLE Departments (
  DepartmentID int PRIMARY KEY,
  DepartmentName varchar(255)
);

CREATE TABLE Employees (
  EmployeeID int PRIMARY KEY,
  FirstName varchar(255),
  LastName varchar(255),
  DepartmentID int,
  FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

The FOREIGN KEY constraint connects employees to departments, ensuring that each employee belongs to a valid department.

Example 4:

In a more complex scenario, consider tables “Orders” and “Customers,” where each order is associated with a customer:

CREATE TABLE Customers (
  CustomerID int PRIMARY KEY,
  FirstName varchar(255),
  LastName varchar(255)
);

CREATE TABLE Orders (
  OrderID int PRIMARY KEY,
  OrderDate date,
  CustomerID int,
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

The FOREIGN KEY constraint establishes a link between orders and customers, ensuring that each order is associated with a valid customer.

DROP a DEFAULT Constraint

To drop a DEFAULT constraint, use the following SQL:

MySQL:

ALTER TABLE Persons
ALTER City DROP DEFAULT;

SQL CHECK Constraint:

The SQL CHECK constraint is used to limit the range of values that can be inserted into a column. It ensures that the values in a column satisfy a specified condition, helping to maintain data integrity.

You can think about it like a filter, to filter and choose the data carefully

Simple Explanation:

The CHECK constraint restricts the values that can be inserted into a column based on a specified condition, ensuring data validity.

Example 1:

Consider a table “Students” where you want to ensure that the age of a student is between 18 and 25 years:

CREATE TABLE Students (
  StudentID int PRIMARY KEY,
  FirstName varchar(255),
  LastName varchar(255),
  Age int CHECK (Age BETWEEN 18 AND 25)
);

In this example, the CHECK constraint ensures that only students between the ages of 18 and 25 can be added to the table.

Example 2:

Continuing with the “Students” table, let’s restrict the enrollment year to be between 2000 and the current year:

CREATE TABLE Students (
  StudentID int PRIMARY KEY,
  FirstName varchar(255),
  LastName varchar(255),
  EnrollmentYear int CHECK (EnrollmentYear BETWEEN 2000 AND YEAR(GETDATE()))
);

The CHECK constraint here ensures that the enrollment year is within a valid range.

Example 3:

Consider a table “Employees” where you want to limit the salary to be above a minimum value:

CREATE TABLE Employees (
  EmployeeID int PRIMARY KEY,
  FirstName varchar(255),
  LastName varchar(255),
  Salary decimal(10, 2) CHECK (Salary > 0)
);

The CHECK constraint guarantees that an employee’s salary is a positive value.

Feel free to connect 🌹me on:

Twitter: https://twitter.com/Noransaber11

Linkedin: https://www.linkedin.com/in/noran-saber-abdelfattah-6198471ba/

Github: https://github.com/Noransaber

Sincerely, Noran🌹