Assignment 3 (constraints)
Assignment 3
📘 Practical 3 – Theory & Concepts
Objective:
To implement constraints on a table.
1. Data Constraints
Constraints are rules applied on table columns to ensure valid data entry.
They can be applied at:
Type | Description |
|---|---|
Column Level Constraint | Written along with the column definition |
Table Level Constraint | Written separately after column definitions (used when constraints depend on multiple columns) |
NULL Constraint
A column can store NULL values unless defined as NOT NULL.
Syntax:
CREATE TABLE table_name (
column_name datatype(size) NOT NULL
);
PRIMARY KEY Constraint
Uniquely identifies each record
Cannot be NULL
Can be single or multiple columns (composite PK)
Column Level Primary Key
CREATE TABLE table_name (
column_name datatype(size) PRIMARY KEY
);
Table Level Primary Key
CREATE TABLE table_name (
col1 datatype(size),
col2 datatype(size),
PRIMARY KEY(col1, col2)
);
DEFAULT Constraint
Assigns a default value if no value is entered.
CREATE TABLE table_name (
column_name datatype(size) DEFAULT value
);
FOREIGN KEY Constraint
Defines relationship between two tables.
Column Level Foreign Key
CREATE TABLE table_name (
column_name datatype(size) REFERENCES other_table(col_name)
);
Table Level Foreign Key
CREATE TABLE table_name (
column_name datatype(size),
FOREIGN KEY(column_name) REFERENCES other_table(col_name)
);
CHECK Constraint
Ensures data meets a condition before insertion.
CREATE TABLE table_name (
column_name datatype(size)
CONSTRAINT constraint_name CHECK(condition)
);
UNIQUE Constraint
Ensures values in a column are unique.
CREATE TABLE table_name (
column_name datatype(size) UNIQUE
);
🧪 Assignment #3 – All Queries + Expected Output (Single Block)
Department table must be created first because Employee table references it.
-- 1. Create Department Table
CREATE TABLE Department (
Deptid NUMBER(4) PRIMARY KEY,
Department_name VARCHAR2(30) NOT NULL
);
-- 2. Create Employee Table
CREATE TABLE Employee (
Employee_id NUMBER(6) PRIMARY KEY,
First_name VARCHAR2(10) NOT NULL,
Last_name VARCHAR2(10) NOT NULL,
Email VARCHAR2(25) UNIQUE NOT NULL,
Phone_no VARCHAR2(20),
Hiredate DATE NOT NULL,
Job_id VARCHAR2(20) NOT NULL,
Salary NUMBER(8,2) DEFAULT 5000 CHECK (Salary > 0),
Comm NUMBER(5,2),
Manager_id NUMBER(6) REFERENCES Employee(Employee_id),
Deptid NUMBER(4) REFERENCES Department(Deptid)
);
-- 3. Insert Data
INSERT INTO Department VALUES (10, 'Sales');
INSERT INTO Department VALUES (20, 'HR');
INSERT INTO Employee VALUES (101, 'Amit', 'Sharma', 'amit@gmail.com', '9876543210', '12-JAN-2024', 'DEV', 12000, NULL, NULL, 10);
INSERT INTO Employee VALUES (102, 'Ravi', 'Verma', 'ravi@gmail.com', '9898989898', '05-FEB-2024', 'HR', 8000, 5, 101, 20);
-- 4. Retrieve Data From Both Tables
SELECT * FROM Department;
SELECT * FROM Employee;
-- Expected Output (Department)
-- Deptid | Department_name
-- 10 | Sales
-- 20 | HR
-- Expected Output (Employee)
-- 101 | Amit | Sharma | amit@gmail.com | 9876543210 | 12-JAN-24 | DEV | 12000 | NULL | NULL | 10
-- 102 | Ravi | Verma | ravi@gmail.com | 9898989898 | 05-FEB-24 | HR | 8000 | 5 | 101 | 20
-- 5. Show All Constraints Applied on Employee & Department Tables
SELECT constraint_name, constraint_type, table_name
FROM user_constraints
WHERE table_name IN ('EMPLOYEE', 'DEPARTMENT');
-- Expected Output (Sample)
-- Constraint_Name | Type | Table_Name
-- SYS_C0012345 | P | DEPARTMENT
-- SYS_C0012346 | P | EMPLOYEE
-- SYS_C0012347 | U | EMPLOYEE (Email Unique)
-- SYS_C0012348 | C | EMPLOYEE (Salary > 0 Check)
-- SYS_C0012349 | R | EMPLOYEE (FK to Department)
-- SYS_C0012350 | R | EMPLOYEE (FK to Employee)