18/03/2026 Lab 5

SQL DDL & Constraints

INSTRUCTOR: Osama Subhani Khan

Lab Rubrics (Group 1)
Criteria Unacceptable Adequate Proficient
(Marks= Zero) Marks= Half Marks= Full
R1 The program failed The program The program was correctly
Completeness and to produce the right was correctly functional, and all the
Accuracy (2 marks) accurate result functional and features were implemented
most of the
features were
implemented
R2 Student failed to Students have Students have
Originality (2 marks) demonstrate a clear basic demonstrated an accurate
understanding of the knowledge of understanding of the lab
assigned task understanding. objective and concepts. All
Answers to the the questions are answered
questions completely and correctly
are basic
R3 Coding standards, Coding Coding standards, best
Demonstration & the best standards, best programming practices are
understanding (3 programming programming followed appropriately.
marks) practices are not practices are
followed. Students rarely
cannot understand followed
the code
R4 Not uploaded Uploaded on Complete and uploaded on
Lab report (3 marks) time but time
incomplete
SQL DDL commands
The DDL commands in SQL are used to create database schemas and to define the type and
structure of the data that will be stored in a database. SQL DDL commands are further divided into
the following major categories:

 CREATE: Creates a new table or database.

 ALTER: Modifies an existing database object.
 DROP: Deletes an entire table, database, or other objects.
 TRUNCATE: Removes all records from a table, deleting the space allocated for the
records.
 Rename: The RENAME command only applies to changing the table's name.
 Comment:

Rename:
In SQL Server, there is no direct RENAME command, but you can rename a table or column
using the following methods:

Rename a table:
Syntax:
EXEC sp_rename 'old_table_name', 'new_table_name';

Example:
Rename Column
To rename a column, you also use sp_rename, but you must specify the table name along with
the column name.

Syntax:
EXEC sp_rename 'table_name.old_column_name', 'new_column_name', 'COLUMN';

Example:

COMMENT in SQL
Commenting in SQL is crucial for clarity and context, making your queries easier to understand
and maintain. It helps explain complex logic, document changes, and justify certain choices. In
SQL, you can add comments in two ways:

Single-line comments
 Use -- (double hyphen) to comment out a single line.

 Anything after -- on the same line is ignored by SQL Server.

Example:
-- This query selects all employees from the Staff table

SELECT * FROM Staff;

Multi-line comments
 Use /* ... */ to comment out multiple lines.
 Everything between /* and */ is ignored by SQL Server.

Example:
/*

This query retrieves all staff members

who joined before 2023.

*/

SELECT * FROM Staff

WHERE JoinDate < '2023-01-01';

SQL Constraints
SQL Constraints are rules applied to table columns to enforce data integrity and ensure the
accuracy and reliability of the database. Constraints prevent invalid data entry and maintain
relationships between tables.

Primary Key constraint

A Primary Key is a column (or a set of columns) that uniquely identify each record in a table.
Characteristics of a Primary Key:
 Must contain unique values.

 Cannot be NULL.

 A table can have only one Primary Key, but it can consist of multiple columns
(composite key).

Example:

CREATE TABLE Customers (

CustomerID INT PRIMARY KEY,
Name VARCHAR(100),
Email VARCHAR(100) UNIQUE
);

Foreign key
A Foreign Key is a column (or a set of columns) in one table that refers to the Primary Key of
another table.
It establishes a relationship between the two tables.

Characteristics of a Foreign Key:

  Can have duplicate values.

 Can contain NULL (unless explicitly set to NOT NULL).

 Changes in the referenced table affect the foreign key table if CASCADE actions are
defined.

Example:

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

We can define CASCADE actions for Foreign Keys:

 ON DELETE CASCADE → Deletes records in the child table when the parent record is
deleted.

 ON UPDATE CASCADE → Updates the Foreign Key value in the child table if the
Primary Key in the parent table changes.

Example:

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON
DELETE CASCADE
);
If a customer is deleted from Customers, all their orders in Orders will be automatically
deleted.

Check Constraint while creating new table

The CHECK constraint ensures that all values in a column satisfy certain conditions.

Example:
CREATE TABLE Students (

StudentID int PRIMARY KEY,

Age int CHECK (Age >= 18)

);
This command ensures that the Age of students is always 18 or older.

Adding a CHECK Constraint to an Existing Table

ALTER TABLE Students

ADD CONSTRAINT chk_Age CHECK (Age >= 18);

Verifying the CHECK Constraint

INSERT INTO Students (StudentID, FirstName, LastName, Age)

VALUES (6, 'Tom', 'Holland', 16); -- This should fail because Age < 18

Output:

Use of Default & Not Null Constraint in Databases

Default Constraint:
The DEFAULT constraint sets a default value for a column when no value is specified.

Example:
CREATE TABLE Students (

StudentID int PRIMARY KEY,

EnrollmentDate date DEFAULT GETDATE ()

);
This command sets the default enrollment date to the current date.
Applying a DEFAULT Constraint on an Existing Table
Syntax: ALTER TABLE TableName ADD CONSTRAINT ConstraintName DEFAULT
DefaultValue FOR ColumnName;

Example:
Let’s set the default age to 18 in the Students table:

ALTER TABLE Students

ADD CONSTRAINT df_Age DEFAULT 18 FOR Age;

Now test above command,

INSERT INTO Students (StudentID, FirstName, LastName,Age) VALUES (6, 'Tom', 'Holland');

Output:

Not Null Constraint:

The NOT NULL constraint ensures that a column cannot have a NULL value.

Example:
CREATE TABLE Students (

StudentID int PRIMARY KEY,

FirstName varchar(255) NOT NULL

);

This command ensures that the FirstName column cannot have a NULL value.
Naming the Constraints
Constraints can be explicitly named to make them easier to identify and manage.

Example:

CREATE TABLE Employees (

EmpID INT CONSTRAINT PK_Employees PRIMARY KEY, -- Named Primary Key

FirstName VARCHAR(50) NOT NULL,

LastName VARCHAR(50) NOT NULL,

Age INT CONSTRAINT CHK_Age CHECK (Age >= 18), -- Named CHECK Constraint

DepartmentID INT,

Salary DECIMAL(10,2) CONSTRAINT DF_Salary DEFAULT 30000, -- Named DEFAULT

Constraint

CONSTRAINT FK_Department FOREIGN KEY (DepartmentID) REFERENCES

Departments(DeptID) -- Named Foreign Key

);

Explanation of Named Constraints

Constraint Name Purpose

Primary Key PK_Employees Ensures each EmpID is unique

Check Constraint CHK_Age Ensures Age is always >= 18

Default Constraint DF_Salary Sets a default Salary = 30,000 if not provided

Foreign Key FK_Department Links DepartmentID to Departments table

Applying Multiple Constraints on One Field

A column can have multiple constraints applied to it, ensuring it meets various criteria.
Example:
CREATE TABLE Students (

StudentID int PRIMARY KEY,

FirstName varchar(255) NOT NULL CHECK (LEN(FirstName) > 1)

);
This command applies both a NOT NULL and a CHECK constraint on the FirstName column.

Using Alter Statement to Add or Drop the Constraints

Dropping Constraint
The ALTER TABLE statement can also be used to drop existing constraints.

Example:
ALTER TABLE Students

DROP CONSTRAINT CHK_Age;

This command drops the CHK_Age constraint from the Students table.

Deferred Constraint Checking (Chicken Egg Problem)

Detailed explanation on deferred constraint and chicken-egg problem is given in the link below:

[Link]
 LAB TASK
Consider a database named "CollegeDB" with the following requirements:
1) Create a table named Courses with columns CourseID (int), CourseName (varchar), and
Credits (int). Set CourseID as the primary key.
2) Create a table named Enrollments with columns EnrollmentID (int), StudentID (int),
CourseID (int), and Grade (char).
3) Set EnrollmentID as the primary key.
4) Set a foreign key on StudentID referencing the student’s table.
5) Set a foreign key on CourseID referencing the Courses table.
6) Apply a CHECK constraint on the Credits column to ensure it's between 1 and 5.
7) Add a NOT NULL constraint to the CourseName column in the Courses table.
8) Name the primary key constraint on CourseID as PK_CourseID.
9) Insert three valid records into the Courses table.
10) Insert an invalid record into Courses (e.g., a course with Credits = 6) and observe the
CHECK constraint violation.
11) Insert three valid records into the Enrollments table.
12) Insert an invalid record into Enrollments (e.g., a record with StudentID that does not exist
in the Students table) and observe the Foreign Key constraint violation.
13) Drop the CHECK constraint on the Credits column.
14) Display all records from both tables to verify the results.
Write SQL script(s) that accomplish the above tasks.