PostgreSql Day-4 Foreign key, Sql Commands, Delete cascade

Table using primary key:

create table citizens(aadhar_no integer, pan_no text, name text, primary key(aadhar_no, pan_no));

GENERATED ALWAYS AS IDENTITY
The GENERATED ALWAYS AS IDENTITY clause is used to auto-generate unique values for a column, typically for primary keys.

create table customers(customer_id int GENERATED ALWAYS AS IDENTITY, customer_name text not null, primary key(customer_id));

Inserting values into customers table:

insert into customers(customer_name) values('guru');

Foreign key

--> A foreign key is a column (or a group of columns) in one table that references the primary key of another table, establishing a link between the two tables.
--> The table containing the foreign key is known as the “child table” and the table to which it refers is known as the “parent table.”

DELETE CASCADE

--> DELETE CASCADE is used to automatically remove child records when the parent record is deleted.

--> It is typically defined on foreign key constraints.

CREATE TABLE contacts
(contact_id int GENERATED ALWAYS AS IDENTITY, 
customer_id int, 
contact_name text not null, 
mobile int, 
email text, 
PRIMARY KEY contact_id, 
CONSTRAINT fk_customer  FOREIGN KEY(customer_id) REFERENCES customers(CUSTOMER_ID) 
on DELETE CASCADE
);

So if parent record is deleted child record will also be deleted.

delete from customers where customer_id=1;

 customer_id | customer_name 
-------------+---------------
           2 | pritha
           3 | guru
(2 rows)

insert into contacts(customer_id, contact_name, mobile, email) values (1,'Guru', 1234, '[e-mail]'), (2, 'Pritha', 2345, '[e-mail]');

After deleting it shows error if we try to insert values to the parent deleted records along with child.

movie=# insert into contacts(customer_id, contact_name, mobile, email) values (1,'Guru', 1234, '[e-mail]'), (2, 'Pritha', 2345, '[e-mail]');
ERROR:  insert or update on table "contacts" violates foreign key constraint "fk_customer"
DETAIL:  Key (customer_id)=(1) is not present in table "customers".
movie=# 

SERIAL

SERIAL is a pseudo data type that auto-increments values for a column, typically used for primary keys.

Example:2

movie=# create table students_2
(student_id SERIAL primary key, 
name varchar(20) not null, 
class int);
CREATE TABLE

movie=# create table courses
(course_id SERIAL primary key, 
student_id int, 
course_name text, 
FOREIGN KEY (student_id) REFERENCES students_2(student_id) on DELETE CASCADE);
CREATE TABLE

movie=# insert into students_2 values(101,'guru',4),(102,'pritha',5),(103,'kuhan',3);
INSERT 0 3


movie=# insert into courses(student_id, course_name) values(101, 'Karate'), (102,'Kung fu'), (103,'Yoga');
INSERT 0 3


movie=# delete from students_2 where student_id = 102;
DELETE 1

movie=# select * from courses;
 course_id | student_id | course_name 
-----------+------------+-------------
         4 |        101 | Karate
         6 |        103 | Yoga
(2 rows)

movie=# delete from courses where course_id = 4;
DELETE 1

movie=# select * from courses;
 course_id | student_id | course_name 
-----------+------------+-------------
         6 |        103 | Yoga
(1 row)

SQL Commands

1. DDL (Data Definition Language) – Defines the structure of the database

These commands modify the schema or structure of the database.

• CREATE → Creates a new database object (table, schema, index).
• ALTER → Modifies an existing database object (table, column).
• DROP → Deletes a database object (table, database, schema).
• TRUNCATE → Removes all rows from a table but keeps the structure.
• RENAME → Renames a database object.

**2. DQL (Data Query Language) – **Retrieves data from the database

Used for fetching records from the database.

• SELECT → Retrieves data from tables. **
• DML (Data Manipulation Language) –** Modifies data in tables

Used for manipulating existing data.

• INSERT → Adds new records.
• UPDATE → Modifies existing records.
• DELETE → Removes specific records.

4. DCL (Data Control Language) – Controls access to data

Used for user privileges and permissions.

• GRANT → Gives specific permissions to a user.
• REVOKE → Removes specific permissions from a user.

5. TCL (Transaction Control Language) – Manages transactions

Used for handling database transactions.

• COMMIT → Saves all changes made in a transaction permanently.
• ROLLBACK → Reverts changes made in a transaction.
• SAVEPOINT → Creates a point within a transaction to which you can later rollback.

create table:

create table emp(empno numeric, empname text, joining_date date, dept_no numeric);

Insert values to the table:

insert into emp values(101, 'Raja', '26-Feb-2025', 1);

Alter table and adding one more column:

alter table emp add salary int;

Alter table by deleting entire column:

alter table emp drop column dept_no;

Modify Column Data Type in a Table:

ALTER TABLE emp ALTER COLUMN empname TYPE VARCHAR(30);

Update a Specific Record in a Table:

UPDATE emp SET salary = 25000 WHERE empno = 101;

Delete Records with NULL Salary:

DELETE FROM emp WHERE salary IS NULL;

Remove All Records from a Table (Without Dropping Structure):

TRUNCATE TABLE employees;

Drop a Table Permanently:

DROP TABLE employees;

Views:

--> A view in PostgreSQL is a virtual table that is based on the result of a SELECT query.
--> It does not store data itself but dynamically presents data from one or more tables.

Creating emp table and view of that table as emp_view:

movie=# select * from emp;
 empno | empname |    doj     | salary 
-------+---------+------------+--------
   101 | Raja    | 2025-02-26 |  25000
   103 | Kumaran | 2025-02-11 |  32000
   102 | Kumar   | 2025-02-11 |  30000
(3 rows)

movie=# select * from emp_view;
 empno | empname | joining_date | salary 
-------+---------+--------------+--------
   101 | Raja    | 2025-02-26   |  25000
   103 | Kumaran | 2025-02-11   |  32000
   102 | Kumar   | 2025-02-11   |  30000
(3 rows)

Index:

--> An index in a database is a data structure that improves the speed of data retrieval operations on a table.
--> It works like a lookup table, allowing the database engine to quickly locate rows without scanning the entire table.

Explain:

--> The EXPLAIN statement returns the execution plan which PostgreSQL planner generates for a given statement.
--> The EXPLAIN shows how tables involved in a statement will be scanned by index scan or sequential scan, etc.,

movie=# create index empname on emp(empname);
CREATE INDEX

movie=# explain select * from emp;
                     QUERY PLAN                      
-----------------------------------------------------
 Seq Scan on emp  (cost=0.00..1.01 rows=1 width=118)
(1 row)


movie=# explain select * from emp where empname = 'Raja';
                     QUERY PLAN                      
-----------------------------------------------------
 Seq Scan on emp  (cost=0.00..1.01 rows=1 width=118)
   Filter: ((empname)::text = 'Raja'::text)
(2 rows

Drop Table If Exists

drop table if exists employees;

Rename Column in Table

alter table emp rename column joining_date to DOJ;

Insert Data into Table

insert into emp values(103, 'Kumaran', '2025-02-11', 32000);

Add a Check Constraint

alter table emp add check(salary in (25000,30000,35000));

Insert another row:

insert into emp values(102, 'Kumar', '2025-02-11', 30000);