HOW TO USE FOREIGN KEY AND PRIMARY KEY

STEP 1: 

mysql> select * from STUDENT ; 

 Output :

# ID, DOB, STUDENT_NAME
  '1', '2020-01-01', 'A'

 Alter Table : Add new column Gender  in Student table.

mysql> alter table student add column  gender_id int ;

 Output :

# ID        DOB,            STUDENT_NAME    gender_id
    '1',   '2020-01-01',        'A',                           NULL

STEP 2:

CREATE GENDER TABLE  :

mysql> create table gender (ID int primary key,Gender_Name VARCHAR(10) );

mysql> insert into gender values (1,'Male'),(2,'Female'),(3,'U');

mysql>select * from gender ;

# ID, Gender_Name
'1', 'Male'
'2', 'Female'
'3', 'U'

Now Apply foreign key :

mysql> alter table student add constraint fk_gender_id  foreign key (gender_id ) references gender(ID) ;

mysql> update student set gender_id=1  where ID=1
 
IF You are update gender_id =4 it is give error .4 is not exist in gender table .It is take from gender table .

mysql> update student set gender_id=4  where ID=1 ;

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`dev_user`.`student`, CONSTRAINT `fk_gender_id` FOREIGN KEY (`gender_id`) REFERENCES `gender` (`ID`))    0.049 sec

IF You want to drop column Name gender : Firstly drop foreign key column .

mysql>alter table student drop column  gender_id;

Error Code: 1828. Cannot drop column 'gender_id': needed in a foreign key constraint 'fk_gender_id'    0.033 sec

USE THIS CODE FIRST

mysql> alter table student drop foreign key fk_gender_id;