SQL-Basics 6(Primery key,Foreign key,update cascade,delete Cascade)

 ------Primary key and Foreign key ------

create database key_prim;

use key_prim;

Primary Key:-

Table:1

create table ineuron(

course_id int not null,

course_name varchar(60),

course_status varchar(40),

number_of_enr int,

primary key(course_id));

insert into ineuron values(01,'FSDA','active',100);

select * from ineuron;

insert into ineuron values(01,'FSDA','not active',100);

Error:

--------:-

Error Code: 1062. Duplicate entry '1' for key 'ineuron.PRIMARY'

Note:-

---->Bcz primary key sholud be unique(it is not allowed duplicate values).

insert into ineuron values(02,'FSDA','not active',100);

select * from ineuron;

-------->Whenever we want to establish a relationship b/w one table to another table, then we can use 

together primary key and foreign key.

 

Foreign key:-

Table:2

create table students_ineuron(

student_id int,

course_name varchar(60),

student_mail varchar(60),

student_status varchar(60),

course_id1 int,

foreign key(course_id1) references ineuron(course_id));

Note:-

--------->From the below diagram we can check the relationship b/w two tables.

                          ---------Data Modeling B/w Two tables------

insert into students_ineuron values(101,'fsda','test@gmail.com','active',05)

Error:-

--Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`key_prim`.`students_ineuron`, CONSTRAINT `students_ineuron_ibfk_1` FOREIGN KEY (`course_id1`) REFERENCES `ineuron` (`course_id`))

Reason:-

----->The child table which column having the foreign key depends on parent table primary key must be same otherwise it will not allowed to insert date in child table.

Solution:-

insert into students_ineuron values(101,'fsda','test@gmail.com','active',01);

select * from students_ineuron;

Note:-

--->We can create multiple columns or groups of columns as primary keys.

---->primary is should be not null and must be unique.

---->But foreign key allows the duplicate values like below.

insert into students_ineuron values(101,'fsda','test@gmail.com','active',01);

insert into students_ineuron values(101,'fsda','test@gmail.com','active',01);

insert into students_ineuron values(101,'fsda','test@gmail.com','active',01);

select * from students_ineuron;

select count(*) from students_ineuron;

Note:-

------>with the help of the primary key and foreign key we can build the relationship one too many,

one-to-one, many-to-many relations.

Table:-3

create table payment(

course_name varchar(60),

course_id int,

course_live_status varchar(60),

course_launch_date varchar(60),

foreign key(course_id) references ineuron(course_id));

insert into payment values('fsda',06,'not active','14th sep');

Error:

#Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`key_prim`.`payment`, CONSTRAINT `payment_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `ineuron` (`course_id`))

insert into payment values('fsda',01,'not active','14th sep')

insert into payment values('fsda',01,'not active','14th sep')

insert into payment values('fsda',01,'not active','14th sep')

create table class(

course_id int,

class_name varchar(60),

class_topic varchar(60),

class_duration int,

primary key(course_id),

foreign key(course_id) references ineuron(course_id));

-----------How to add primary key after If table already exist---------------

alter table ineuron add constraint test_prim primary key(course_id,course_name)

Error:-

Error Code: 1068. Multiple primary key defined.

Note:

---->It is not allowing the multiple primary keys, Bcz table already has the primary key.

alter table ineuron add constraint test_prim primary key(course_status,course_name);

Error:-

Error Code: 1068. Multiple primary key defined.

---------The workaround for this issue we need to drop the primary key------------------

alter table ineuron drop primary key;

Error:-

Error Code: 1553. Cannot drop index 'PRIMARY': needed in a foreign key constraint.

alter table class drop primary key;

Error:

Error Code: 1553. Cannot drop index 'PRIMARY': needed in a foreign key constraint.

----->Bcz it is not allowing to drop,it is already associated with a foreign key

drop table ineuron;

 Error:

Error Code: 3730. Cannot drop table 'ineuron' referenced by a foreign key constraint 'students_ineuron_ibfk_1' on table 'students_ineuron'.

 drop table class;

 ---->Now am able to drop the child table not the parent table.

----->If we want to drop the parent table first we need to drop all the dependent child tables.

create table test(

id int not null,

name varchar(60),

email_id varchar(60),

mobile_no varchar(9),

address varchar(60))

--------How to create the primary key if the table already exists-----

alter table test add primary key(id);

-------How to drop the primary key for a table--------------

alter table test drop primary key;

--------How to add the primary key for the multiple columns-----------

alter table test add constraint test_prim primary key(id,email_id)

     -------Update Casecade & Delete Cascade---------- 

create table parent(

id int not null,

primary key(id));

create table child(

id int,

parent_id int,

foreign key(parent_id) references parent(id));

insert into parent values(1);

select * from parent;

select count(*) from parent;

insert into child values(1,1);

select * from child;

insert into child values(1,2);

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`key_prim`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))

insert into child values(2,2);

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`key_prim`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))

delete from parent where id=1;

Error:-

 Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`key_prim`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))

delete from child where id=1;

select * from child;

delete from parent where id=1;

drop table child;

create table child(

id int,

parent_id int,

foreign key(parent_id) references parent(id) on delete cascade )

delete cascade:-

---->By mentioning this delete cascade at the time of  creating the foreign key between 

parent and child table.

---->Then it will allow deleting the row in the parent table.

insert into child values(1,1),(1,2),(3,2),(2,2);

--Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`key_prim`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE)

insert into child values(1,1),(1,2),(3,2),(2,2);

select * from parent;

select * from child;

delete from parent where id=1;

select * from parent;

update parent set id=3 where id=2

Error:

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`key_prim`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE)

drop table child;

create table child(

id int,

parent_id int,

foreign key(parent_id) references parent(id) on update cascade )

update cascade:-

---->By mentioning this update cascade at the time of  creating the foreign key between 

parent and child table.

---->Then it will allow you to update the row in the parent table.

insert into child values(1,1),(1,2),(3,2),(2,2);

Error:

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`key_prim`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON UPDATE CASCADE)

insert into parent values(1);

insert into child values(1,1),(1,2),(3,2),(2,2)

select * from child;

select * from parent;

update parent set id=3 where id=2

select * from child;

select * from parent;

create table child1(

id int,

parent_id int,

foreign key(parent_id) references parent(id) on update cascade on delete cascade )

Data modeling:-

--->one table associated with another table this is called data modeling.

In real-time scenario:-

----->Generally what happens is , whenever we join in any organization they will give us complete data modeling strategy(or)In the very beginning whenever we are trying to create databases

---->First we need to design the model that what is going to be the primary key and what is 

going to a foreign key,  whether it is going to be one to one relation (or) one to many realtion

(or) many to many relations we always think about that then which column is going behave

to as a primary key to which table and which column is going to behave foreign key on which table

we need to try to identify that as well as and based on that we are trying to create our DB

entire model.

---->After creating the data model we start inserting the dataset into the respective tables this 

is the ideal approach everyone using in all the industry. (everywhere/every project) all are following

same manner.

create table inueron(

course_id int not null,

course_name varchar(30),

course_status varchar(30),

no_of_enr int,

primary key(course_id))

create table students_ineuron(

  student_id int,

  course_name varchar(60),

  student_mail varchar(60),

  student_status varchar(60),

  course_id1 int,

  foreign key(course_id1) references ineuron(course_id))

  

  Error Code: 1824. Failed to open the referenced table 'ineuron'

Reason:-

---->bcz of the parent table name is different.