DBMS Assignment 3
Nemish Mogra
SY 11
Roll.No 25
AIM : Apply following Integrity Constraints on Database.
1) Not Null Constraint
2)Check Constraint
3)Primary Key and Unique Constraint
4)Foreign Key Constraint
INDEXTERMS:DDL, Integrity Constraint
Code
mysql> CREATE DATABASE CollegeDB;
Query OK, 1 row affected (0.02 sec)
mysql> USE CollegeDB;
Database changed
mysql>
mysql> CREATE TABLE Staff (
-> StaffID INT PRIMARY KEY,
-> StaffName VARCHAR(50) NOT NULL,
-> Salary DECIMAL(10,2) CHECK (Salary > 0),
-> Email VARCHAR(100) UNIQUE
-> );
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> CREATE TABLE Courses (
-> CourseID INT PRIMARY KEY,
-> Fees DECIMAL(10,2) CHECK (Fees > 0),
-> StaffID INT,
-> FOREIGN KEY (StaffID) REFERENCES Staff(StaffID) ON DELETE CASCADE
-> );
Query OK, 0 rows affected (0.04 sec)
mysql>
mysql> INSERT INTO Staff VALUES (1, 'Ravi', 45000, 'ravi@college.com');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO Staff VALUES (2, 'Meera', 55000, 'meera@college.com');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO Courses VALUES (101, 15000, 1);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO Courses VALUES (102, 20000, 2);
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> INSERT INTO Staff VALUES (3, NULL, 48000, 'nullstaff@college.com');
ERROR 1048 (23000): Column 'StaffName' cannot be null
mysql> INSERT INTO Staff VALUES (4, 'Kiran', -1200, 'kiran@college.com');
ERROR 3819 (HY000): Check constraint 'staff_chk_1' is violated.
mysql> INSERT INTO Staff VALUES (1, 'Duplicate', 60000, 'dup@college.com');
ERROR 1062 (23000): Duplicate entry '1' for key 'staff.PRIMARY'
mysql> INSERT INTO Staff VALUES (5, 'Anita', 50000, 'ravi@college.com');
ERROR 1062 (23000): Duplicate entry 'ravi@college.com' for key 'staff.Email'
mysql> INSERT INTO Courses VALUES (103, 18000, 10);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails (`collegedb`.`courses`, CONSTRAINT `courses_ibfk_1` FOREIGN KEY (`StaffID`)
REFERENCES `staff` (`StaffID`) ON DELETE CASCADE)
mysql>
mysql> SELECT * FROM Staff;
+---------+-----------+----------+-------------------+
| StaffID | StaffName | Salary | Email |
+---------+-----------+----------+-------------------+
| 1 | Ravi | 45000.00 | ravi@college.com |
| 2 | Meera | 55000.00 | meera@college.com |
+---------+-----------+----------+-------------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM Courses;
+----------+----------+---------+
| CourseID | Fees | StaffID |
+----------+----------+---------+
| 101 | 15000.00 | 1 |
| 102 | 20000.00 | 2 |
+----------+----------+---------+
2 rows in set (0.00 sec)
mysql>
Exercise:
1)Write a query to insert a new customer into the Customers table. Ensure that the
Email is unique and does not duplicate an existing email.
mysql> CREATE DATABASE ShopDB;
Query OK, 1 row affected (0.01 sec)
mysql> USE ShopDB;
Database changed
mysql> CREATE TABLE Customers (
-> CustomerID INT PRIMARY KEY,
-> FirstName VARCHAR(50),
-> LastName VARCHAR(50),
-> Email VARCHAR(100) UNIQUE,
-> PhoneNumber VARCHAR(15)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO Customers VALUES (1, 'Amit', 'Sharma',
'amit.sharma@example.com', '9876543210');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO Customers VALUES (2, 'Ravi', 'Kumar', 'amit.sharma@example.com',
'9999999999');
ERROR 1062 (23000): Duplicate entry 'amit.sharma@example.com' for key
'customers.Email'
mysql>
2) Consider the schema: employee(employee-name, street, city)
works(employee-name, company-name, salary)
company(company-name, city)
manages(employee-name, manager-name)
Give an SQL DDL definition for the tables of this database. Identify referential
integrity
constraints that should hold and include them in the DDL definition.
mysql> CREATE TABLE Employee (
-> employee_name VARCHAR(50) PRIMARY KEY,
-> street VARCHAR(100),
-> city VARCHAR(50)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> CREATE TABLE Company (
-> company_name VARCHAR(50) PRIMARY KEY,
-> city VARCHAR(50)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> CREATE TABLE Works (
-> employee_name VARCHAR(50),
-> company_name VARCHAR(50),
-> salary DECIMAL(10,2),
-> PRIMARY KEY (employee_name, company_name),
-> FOREIGN KEY (employee_name) REFERENCES Employee(employee_name),
-> FOREIGN KEY (company_name) REFERENCES Company(company_name)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql>
mysql> CREATE TABLE Manages (
-> employee_name VARCHAR(50),
-> manager_name VARCHAR(50),
-> PRIMARY KEY (employee_name, manager_name),
-> FOREIGN KEY (employee_name) REFERENCES Employee(employee_name),
-> FOREIGN KEY (manager_name) REFERENCES Employee(employee_name)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO Employee VALUES ('Sita', 'MG Road', 'Delhi');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO Company VALUES ('Infosys', 'Bangalore');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO Works VALUES ('Sita', 'Infosys', 50000);
Query OK, 1 row affected (0.01 sec)
mysql>
mysql>
mysql> INSERT INTO Works VALUES ('Ram', 'Infosys', 45000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails (`shopdb`.`works`, CONSTRAINT `works_ibfk_1` FOREIGN KEY (`employee_name`)
REFERENCES `employee` (`employee_name`))
mysql>
mysql> SELECT * FROM Employee;
+---------------+---------+-------+
| employee_name | street | city |
+---------------+---------+-------+
| Sita | MG Road | Delhi |
+---------------+---------+-------+
1 row in set (0.00 sec)
mysql> SELECT * FROM Company;
+--------------+-----------+
| company_name | city |
+--------------+-----------+
| Infosys | Bangalore |
+--------------+-----------+
1 row in set (0.00 sec)
mysql> SELECT * FROM Works;
+---------------+--------------+----------+
| employee_name | company_name | salary |
+---------------+--------------+----------+
| Sita | Infosys | 50000.00 |
+---------------+--------------+----------+
1 row in set (0.00 sec)
mysql>
3)Write an SQL statement to create an Orders table that ensures the CustomerID
must exist in
the Customers table.
mysql> CREATE DATABASE OrdersDB;
Query OK, 1 row affected (0.01 sec)
mysql> USE OrdersDB;
Database changed
mysql>
mysql> CREATE TABLE Customers (
-> CustomerID INT PRIMARY KEY,
-> FirstName VARCHAR(50),
-> LastName VARCHAR(50),
-> Email VARCHAR(100) UNIQUE
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> CREATE TABLE Orders (
-> OrderID INT PRIMARY KEY,
-> OrderDate DATE,
-> CustomerID INT,
-> TotalAmount DECIMAL(10,2),
-> FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
-> );
Query OK, 0 rows affected (0.04 sec)
mysql>
mysql>
mysql> INSERT INTO Customers VALUES (1, 'Amit', 'Sharma', 'amit@example.com');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO Orders VALUES (101, '2025-09-04', 1, 55000);
Query OK, 1 row affected (0.01 sec)
mysql>
mysql>
mysql> INSERT INTO Orders VALUES (102, '2025-09-04', 5, 40000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails (`ordersdb`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`CustomerID`)
REFERENCES `customers` (`CustomerID`))
mysql>
mysql> SELECT * FROM Customers;
+------------+-----------+----------+------------------+
| CustomerID | FirstName | LastName | Email |
+------------+-----------+----------+------------------+
| 1 | Amit | Sharma | amit@example.com |
+------------+-----------+----------+------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM Orders;
+---------+------------+------------+-------------+
| OrderID | OrderDate | CustomerID | TotalAmount |
+---------+------------+------------+-------------+
| 101 | 2025-09-04 | 1 | 55000.00 |
+---------+------------+------------+-------------+
1 row in set (0.00 sec)
mysql>
4)Write an SQL statement to create a Customers table that enforces unique email
addresses.
mysql> CREATE DATABASE CustomerDB;
Query OK, 1 row affected (0.00 sec)
mysql> USE CustomerDB;
Database changed
mysql>
mysql> CREATE TABLE Customers (
-> CustomerID INT PRIMARY KEY,
-> FirstName VARCHAR(50),
-> LastName VARCHAR(50),
-> Email VARCHAR(100) UNIQUE,
-> PhoneNumber VARCHAR(15)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> -- Valid insert
mysql> INSERT INTO Customers VALUES (1, 'Neha', 'Verma', 'neha.verma@example.com',
'9876543210');
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> -- Invalid insert (❌ duplicate Email)
mysql> INSERT INTO Customers VALUES (2, 'Raj', 'Kumar', 'neha.verma@example.com',
'9999999999');
ERROR 1062 (23000): Duplicate entry 'neha.verma@example.com' for key
'customers.Email'
mysql>
mysql> SELECT * FROM Customers;
+------------+-----------+----------+------------------------+-------------+
| CustomerID | FirstName | LastName | Email | PhoneNumber |
+------------+-----------+----------+------------------------+-------------+
| 1 | Neha | Verma | neha.verma@example.com | 9876543210 |
+------------+-----------+----------+------------------------+-------------+
1 row in set (0.00 sec)
mysql>
5) Consider following database schemas
Customers:
○ CustomerID (Primary Key)
○ FirstName
○ LastName
○ Email(Unique)
○ PhoneNumber
2. Products:
○ ProductID (Primary Key)
○ ProductName
○ Price
○ StockQuantity (Must be non-negative)
3. Orders:
○ OrderID (Primary Key)
○ OrderDate
○ CustomerID (Foreign Key referencing Customers.CustomerID)
○ TotalAmount
4. OrderDetails:
○ OrderDetailID (Primary Key)
○ OrderID (Foreign Key referencing Orders.OrderID)
○ ProductID (Foreign Key referencing Products.ProductID)
○ Quantity (Must be greater than 0)
○ Price
Questions:
1. Scenario 1: A new product is added to the inventory with a negative stock
quantity by
mistake. Describe the constraint that will prevent this, and write the SQL
statement that
ensures the stock quantity cannot be negative.
2. Scenario 2: An order is placed by a customer, but the customer ID entered does
not exist in
the Customers table. Describe the constraint that will prevent this, and write the
SQL
statement that will enforce this rule.
3. Scenario 3: A customer tries to create an account using an email that is
already in use by
another customer. Describe the constraint that will prevent this, and write the
SQL
statement that ensures email addresses are unique.
4. Scenario 4: An order detail is added with a quantity of 0. Describe the
constraint that will
prevent this, and write the SQL statement to enforce a minimum quantity of 1 for
each
order detail
mysql> USE ShopScenarioDB;
Database changed
mysql>
mysql> CREATE TABLE Customers (
-> CustomerID INT PRIMARY KEY,
-> FirstName VARCHAR(50),
-> LastName VARCHAR(50),
-> Email VARCHAR(100) UNIQUE,
-> PhoneNumber VARCHAR(15)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> CREATE TABLE Products (
-> ProductID INT PRIMARY KEY,
-> ProductName VARCHAR(100),
-> Price DECIMAL(10,2),
-> StockQuantity INT CHECK (StockQuantity >= 0)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> CREATE TABLE Orders (
-> OrderID INT PRIMARY KEY,
-> OrderDate DATE,
-> CustomerID INT,
-> TotalAmount DECIMAL(10,2),
-> FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> CREATE TABLE OrderDetails (
-> OrderDetailID INT PRIMARY KEY,
-> OrderID INT,
-> ProductID INT,
-> Quantity INT CHECK (Quantity > 0),
-> Price DECIMAL(10,2),
-> FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
-> FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
-> );
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> INSERT INTO Customers VALUES (1, 'Amit', 'Sharma',
'amit.sharma@example.com', '9876543210');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO Products VALUES (101, 'Laptop', 55000, 10);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO Orders VALUES (201, '2025-09-04', 1, 55000);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO OrderDetails VALUES (301, 201, 101, 1, 55000);
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> INSERT INTO Products VALUES (102, 'Phone', 25000, -5);
ERROR 3819 (HY000): Check constraint 'products_chk_1' is violated.
mysql> INSERT INTO Orders VALUES (202, '2025-09-04', 5, 30000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails (`shopscenariodb`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY
(`CustomerID`) REFERENCES `customers` (`CustomerID`))
mysql> INSERT INTO Customers VALUES (2, 'Ravi', 'Kumar', 'amit.sharma@example.com',
'9999999999');
ERROR 1062 (23000): Duplicate entry 'amit.sharma@example.com' for key
'customers.Email'
mysql> INSERT INTO OrderDetails VALUES (302, 201, 101, 0, 10000);
ERROR 3819 (HY000): Check constraint 'orderdetails_chk_1' is violated.
mysql>
mysql> SELECT * FROM Customers;
+------------+-----------+----------+-------------------------+-------------+
| CustomerID | FirstName | LastName | Email | PhoneNumber |
+------------+-----------+----------+-------------------------+-------------+
| 1 | Amit | Sharma | amit.sharma@example.com | 9876543210 |
+------------+-----------+----------+-------------------------+-------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM Products;
+-----------+-------------+----------+---------------+
| ProductID | ProductName | Price | StockQuantity |
+-----------+-------------+----------+---------------+
| 101 | Laptop | 55000.00 | 10 |
+-----------+-------------+----------+---------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM Orders;
+---------+------------+------------+-------------+
| OrderID | OrderDate | CustomerID | TotalAmount |
+---------+------------+------------+-------------+
| 201 | 2025-09-04 | 1 | 55000.00 |
+---------+------------+------------+-------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM OrderDetails;
+---------------+---------+-----------+----------+----------+
| OrderDetailID | OrderID | ProductID | Quantity | Price |
+---------------+---------+-----------+----------+----------+
| 301 | 201 | 101 | 1 | 55000.00 |
+---------------+---------+-----------+----------+----------+
1 row in set (0.00 sec)
mysql>
FAQs
1) Types of Integrity Constraints
* Domain Integrity → `NOT NULL`, `CHECK`
* Entity Integrity → `PRIMARY KEY`, `UNIQUE`
* Referential Integrity → `FOREIGN KEY`
2) Can a table have multiple Integrity Constraints?
* Yes
* A column can have more than one constraint (e.g., `NOT NULL` + `UNIQUE`).
* Ensures stricter data accuracy.
3) How do Integrity Constraints affect database performance?
* Constraints are checked during `INSERT` or `UPDATE`.
* May cause slight slowdown in large transactions.
* Improves data quality and consistency.
* Saves time in error detection and correction.
4) Can Integrity Constraints be disabled?
* Yes, they can be temporarily disabled.
* Useful during bulk data load or migration.
* Example:
ALTER TABLE Orders DISABLE KEYS;
ALTER TABLE Orders ENABLE KEYS;
5) What happens if a data entry violates an Integrity Constraint?
* Operation is rejected.
* Database shows an error message.
* No invalid data is stored in the table.
