Experiment No: 1 Date: 21-07-25
Aim: To study the following:
• Datatypes, Constraints and Default Values in MySQL
• DDL commands: CREATE, ALTER, DROP,RENAME, TRUNCATE
Theory:
Datatypes in MySql
1. Numeric Data Types
• INT — Integer numbers (e.g., 123, -456)
• SMALLINT — Smaller range integers
• TINYINT — Very small integers (often used for boolean)
• BIGINT — Large integers
• DECIMAL(M, D) — Fixed-point numbers with precision (e.g., 10.25)
• FLOAT — Floating-point numbers (approximate)
• DOUBLE — Double-precision floating-point numbers
2. String (Character) Data Types
• VARCHAR(n) — Variable-length string (up to n characters)
• CHAR(n) — Fixed-length string (exactly n characters)
• TEXT — Large text data
• ENUM — List of predefined values
3. Date and Time Data Types
• DATE — Date only (YYYY-MM-DD)
• DATETIME — Date and time (YYYY-MM-DD HH:MM:SS)
• TIMESTAMP — Timestamp with timezone awareness
• TIME — Time only (HH:MM:SS)
• YEAR — Year (YYYY)
4. Other Data Types
• BLOB — Binary large objects (for storing binary data like images)
• BOOLEAN — Stored as TINYINT(1) (0 = false, 1 = true)
DDL commands: CREATE, ALTER, DROP, RENAME, TRUNCATE
The CREATE TABLE Command
A CREATE TABLE statement creates a table. Tables contain columns and constraints, rules to
which data must conform. Columns have a data type and can specify column constraints
Syntax:
CREATE TABLE emp (
eno SMALLINT PRIMARY KEY,
ename VARCHAR(25) NOT NULL,
salary INT NOT NULL,
hire_date DATE,
gender CHAR DEFAULT 'M',
email VARCHAR(50)
);
The ALTER TABLE Command
Use the ALTER TABLE statement to alter the structure of a table.The alter table command is
used to alter columns/datatypes/constraints of an existing relation.
Syntax:
ALTER TABLE table_name;
Eg.
ADD COLUMN bdate DATE AFTER ename;
The DROP TABLE Command
The DROP command is used to permanently delete a database object like a table,
column, or database.
Syntax:
DROP TABLE table_name;
Eg.
ALTER TABLE employee
DROP COLUMN bdate;
The RENAME Command
Rename command is used for changing the name of a database object (like a table or
column) to a new name without affecting its data or structure.
Syntax:
RENAME TABLE old_table_name TO new_table_name;
Eg,
RENAME TABLE employee TO emp;
The TRUNCATE TABLE Command
The TRUNCATE TABLE command is used to quickly delete all rows from a table without
removing its structure.
Syntax:
TRUNCATE TABLE table_name;
Eg.
TRUNCATE TABLE employee;
Exercise I:
• Create the following table
Employee
Name Constraint Data Type
empno Primary key smallint
ename Not null varchar(25)
salary smallint
hire_date date
gender char
email varchar(50)
• Display the structure of the table employee
• Add a new column bdate to employee table with the datatype date after the column
name
• Rename the column empno to eno
• Change the data type of salary from smallint to int
• Add NOT NULL constraint to salary column of the table
• Reposition column hire_date after column ename
• Drop the newly added column bdate from employee table
• Rename Employee table to Emp
• Set gender to a default value ‘M’
• Create a table dummy_emp from emp table
Specifying Constraints and Default Values in MySQL
……
Following are the most used constraints that can be applied to a table.
• NOT NULL
• UNIQUE
• CHECK
• PRIMARY KEY
• FOREIGN KEY
• DEFAULT
…….
Referential Integrity Constraints
Different tables in a relational database can be related by common columns, and the rules that
govern the relationship of the columns must be maintained. Referential integrity rules guarantee
that these relationships are preserved.
The following terms are associated with referential integrity constraints.
Term Definition
Foreign key The column or set of columns included in the definition of the referential
integrity constraint that reference a referenced key.
Referenced key The unique key or primary key of the same or different table that is
referenced by a foreign key.
Dependent or The table that includes the foreign key. Therefore, it is the table that is
child table dependent on the values present in the referenced unique or primary key.
Referenced or The table that is referenced by the child table's foreign key. It is this table's
parent table referenced key that determines whether specific inserts or updates are
allowed in the child table.
A referential integrity constraint requires that for each row of a table, the value in the
foreign key matches a value in a parent key
Figure below shows a foreign key defined on the department_id column of the employees table.
It guarantees that every value in this column must match a value in the primary key of
the departments table (also the department_id column). Therefore, no erroneous department
numbers can exist in the department_id column of the employees table.
Nulls and Foreign Keys
The relational model permits the value of foreign keys either to match the referenced primary or unique
key value, or be null. If any column of a composite foreign key is null, then the non-null portions of the
key do not have to match any corresponding portion of a parent key.
Actions Defined by Referential Integrity Constraints
Referential integrity constraints can specify particular actions to be performed on the dependent rows in
a child table if a referenced parent key value is modified. The referential actions supported by the
FOREIGN KEY integrity constraints of MySQL are:
1)ON DELETE CASCADE / ON UPDATE CASCADE
When a referenced row in the parent table is deleted or updated, all matching rows in the child table are
automatically deleted or updated.
This is useful for maintaining consistency when related records must always match or be removed
together.
For example, deleting a department deletes all its employees.
2)ON DELETE SET NULL / ON UPDATE SET NULL
If a parent row is deleted or updated, the foreign key value in the child table is set to NULL.
This is allowed only if the foreign key column in the child table permits NULL values.
It preserves the child record but breaks the link to the now-missing parent.
3)ON DELETE RESTRICT / ON UPDATE RESTRICT
Prevents the deletion or update of a parent row if there are matching rows in the child table.
You must delete or update the child records first before modifying the parent.
This enforces strict referential integrity.
4)ON DELETE NO ACTION / ON UPDATE NO ACTION
Behaves the same as RESTRICT in MySQL (though in standard SQL it may differ slightly in timing).
It prevents changes in the parent table if they would break the relationship.
In MySQL, NO ACTION is functionally equivalent to RESTRICT.
…….
Exercise II:
• Create a table DEPT with the following fields: dno, dname, dlocation
• dno should serve as a unique identifier for each record in the table
• no fields should contain null values
• Create a table EMP with the following fields: empno, ename, salary, hire_date,
gender,email, dno
• empno should serve as a unique identifier for each record in the table
• ename and hire_date should not contain null values
• salary of an employee should not be less than Rs.10000
• permissibe values for gender are ‘M’ or ‘F’ default value is ‘M’
• All employees must be associated with a department that is already in the DEPT
table
• Insert the following records in the DEPT table
DEPT
dno dname dlocation
1 Admin Panaji
2 Research Panaji
3 Sales Margao
• Insert the following records in the EMP table
EMP
empno ename salary hire_date gender email dno
101 Jhon 50,000 20-FEB-96 M … 1
102 Bob 1,00,000 15-JUN-95 M … 2
103 Jane 40,000 15-JUN-95 F … 3
104 Luke 50,000 25-APR-98 M …. 1
• Try deleting a department record from DEPT table. What constraint is violated?
• Modify Table EMP add a referential triggered action clause to its foreign key
constraint which will automatically delete or update the matching rows in the child table
when a tuple from the parent table is deleted or updated.
Now try deleting a department record from DEPT table and show the effect it has
on child records
• Modify Table EMP add ON DELETE SET NULL action to its foreign key constraint
Now try deleting a department record from DEPT table and show the effect it has on
child records
