Learning of SQL Day 35

 

Day 35: Data Validation and Constraints

Introduction:

• Data validation and constraints ensure that the data entered into a database is accurate, reliable, and adheres to specific rules and standards. Constraints are rules applied to table columns to enforce data integrity.

Key Concepts:

• NOT NULL: Ensures that a column cannot have a NULL value.

• UNIQUE: Ensures that all values in a column are unique.

• PRIMARY KEY: Uniquely identifies each row in a table.

• FOREIGN KEY: Establishes a relationship between columns in two tables.

• CHECK: Ensures that values in a column meet a specific condition.

• DEFAULT: Sets a default value for a column if no value is specified.

SQL Commands and Examples:

1. NOT NULL Constraint:

   • Ensure that a column cannot have a NULL value.

sql

-- Create a Students table with a NOT NULL constraint on the LastName column
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(255),
    LastName VARCHAR(255) NOT NULL,
    Age INT
);

2. UNIQUE Constraint:

   • Ensure that all values in a column are unique.

sql

-- Create a Courses table with a UNIQUE constraint on the CourseName column
CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(255) UNIQUE,
    Credits INT
);

3. PRIMARY KEY Constraint:

   • Uniquely identify each row in a table.

sql

-- Create an Employees table with a PRIMARY KEY constraint on the EmployeeID column
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(255),
    LastName VARCHAR(255)
);

4. FOREIGN KEY Constraint:

   • Establish a relationship between columns in two tables.

sql

-- Create an Enrollments table with a FOREIGN KEY constraint on the StudentID and CourseID columns
CREATE TABLE Enrollments (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

5. CHECK Constraint:

   • Ensure that values in a column meet a specific condition.

sql

-- Create a Students table with a CHECK constraint to ensure age is between 18 and 65
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(255),
    LastName VARCHAR(255),
    Age INT CHECK (Age BETWEEN 18 AND 65)
);

6. DEFAULT Constraint:

   • Set a default value for a column if no value is specified.

sql

-- Create a Students table with a DEFAULT constraint on the Age column
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(255),
    LastName VARCHAR(255),
    Age INT DEFAULT 18
);

Practice Exercise:

1. Use the Students and Courses tables.

2. Write a query to create a Teachers table with columns TeacherID, FirstName, LastName, CourseID and apply the appropriate constraints.

3. Write a query to add a NOT NULL constraint to the LastName column in the Students table.

4. Write a query to add a UNIQUE constraint to the CourseName column in the Courses table.

5. Write a query to add a CHECK constraint to ensure Credits in the Courses table is greater than 0.

6. Write a query to add a DEFAULT constraint to set the Age column in the Students table to 18.

sql

-- Create a Teachers table with appropriate constraints
CREATE TABLE Teachers (
    TeacherID INT PRIMARY KEY,
    FirstName VARCHAR(255),
    LastName VARCHAR(255) NOT NULL,
    CourseID INT,
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

-- Add a NOT NULL constraint to the LastName column in the Students table
ALTER TABLE Students
MODIFY LastName VARCHAR(255) NOT NULL;

-- Add a UNIQUE constraint to the CourseName column in the Courses table
ALTER TABLE Courses
ADD CONSTRAINT unique_course_name UNIQUE (CourseName);

-- Add a CHECK constraint to ensure Credits in the Courses table is greater than 0
ALTER TABLE Courses
ADD CONSTRAINT check_credits CHECK (Credits > 0);

-- Add a DEFAULT constraint to set the Age column in the Students table to 18
ALTER TABLE Students
ALTER COLUMN Age SET DEFAULT 18;

Important Tips:

• Constraints help maintain data integrity and prevent invalid data from being entered into the database.

• Use appropriate constraints to enforce rules and standards that ensure data accuracy and reliability.

• Regularly review and update constraints as necessary to align with changing data requirements.

Understanding and applying constraints ensures that your database maintains high data integrity and reliability.