Untitled

06/02/2024
------------------------------------------------
Foreign key
------------

foreign key is a one type of contraint. 
it is used for prevent action to destroy link between tables.
foreign key is a field in one table , that refers to the  primary key in another table.

syntax: create table tablename(column1 datataype ,
                              column1 datataype,
                               .
                               .
                              columnn datatype,
                              primary key column of table1 datatype
                              foreign key (primary key column of table1)
                              references table1(primary key column of table1));

exa:-
create table manager1(
             mn_id int primary key ,mname varchar(30),addr varchar(30),salary int);

insert into manager1 values(101,"siva","pune",20000),
(102,"shuhash","nagpur",30000)
,(103,"ajali","nashik",50000),
(104,"neha","pune",170000),
(105,"abhijit","delhi",70000);
select * from manager1;

use forein key in bellow table 

create  table employee(emp_id int , name varchar(30) ,salary int ,age int,mn_id int,primary key (emp_id), foreign key (mn_id) 
references manager1(mn_id));
-----------------------------------------------------------------------------------------------
Add foreign key contraint in the table after table creation.
syntax:-
alter table tablename add foreign key (columnname) references parenttable(columnname)
exa:
alter table employee add foreign key (mn_id) references manager1(mn_id);

----------------------------------------------------------------------------------
delete foreign key from table.
syntax:-
alter table tablename drop foreign key fk_id;
ex:
alter table employee drop foreign key employee_ibfk_1;
=======================================================================================
insert into employee(emp_id , name,salary ,age) values(4,"dghfhjf",6000,22);
insert into employee values(2,"dghfhjf",6000,22,103),(3,"dghfhjf",6000,22,103);


You can obtain information about foreign keys from the Information Schema KEY_COLUMN_USAGE table. An example of a query against this table is shown here:

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
       FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
       WHERE REFERENCED_TABLE_SCHEMA IS NOT NULL;