Untitled

14/02/2024
======================================================================================
foreign key:
---------------------------
A foreign key is a concept that is often used in relational databases. 
It is a way to create a link between two different tables. 
A foreign key is a field that refers to another table's primary key

foreingn key contain duplicate and null value.

syntax:-
create table childtable(column1 value1,contraint,col2 val2, cont2...............pcoloumn datatype ,
    foreign key(pclolumn) references parenttable(pcolumn));


use third;
-- create table customer(cust_id int ,cust_name varchar(222),city varchar(222),age int);
-- alter table customer add primary key(cust_id)

-- create table order1(ord_id int ,item varchar(222),price int,cust_id int,
-- primary key (ord_id),
-- foreign key(cust_id) references customer(cust_id));

select * from order1;
select * from customer;






insert into customer values(101,"ravi","pune",23),
(102,"kajal","satara",34),
(103,"mira","nagpur",45),
(104,"jay","nanded",22);

insert into order1 values(1,"laptop",35000,101),(2,"kebord",30000,101),(3,"mouse",300,104);
insert into order1(ord_id,item) values(4,"mouse")


=====================================================================================================
add foreign key in existing table
syntax: 
alter table tablename add foreign key(column) references parenttable(column)
ex;
alter table order2 add foreign key(cust_id) references customer(cust_id);

alter table order2 add constraint fk_customerorder foreign key(cust_id) references customer(cust_id);


--------------------------------------------------------------------------------------------------------------
drop the foreign key from existing table

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


alter table order2 drop  foreign key fk_customerorder;