/* 022 - PRIMARY KEY & FOREIGN KEY */ --CONCEPT : lest say we have two table Student Table Id Name Age City 1 Ram Kumar 19 Agra 2 Salman Khan 18 Bhopal 3 Meera Khan 19 Agra 4 Sarita Kumari 21 Delhi City Table Cid City 1 Agra 2 Bhopal 3 Delhi -- for making student table fast and light weight -- we will save cid in instead of names in City column in Student table -- for that set Cid and Id to primary key ( follow below command ) ALTER TABLE Student ADD PRIMARY KEY (Id) ; ALTER TABLE City ADD PRIMARY KEY (Cid); -- set City column in student table to foreign key ( follow below command ) ALTER TABLE Student ADD FOREIGN KEY (City) REFERENCES City(Cid); /* CREATE ALL THE ABOVE AT THE TIME OF TABLE CREATION */ -- create table "city" CREATE TABLE city( cid INT NOT NULL AUTO_INCREMENT, -- id of the table auto-increment cityname VARCHAR(50) NOT NULL, PRIMARY KEY (cid) --to create a primary key, must at last ); -- insert records in "city" table INSERT INTO city(cityname) -- id is auto incremental VALUES('Agra'), ('Delhi'), ('Bhopal'), ('Jaipur'), ('Noida'); -- create table "tOne" and insert records CREATE TABLE tOne( id INT NOT NULL, name VARCHAR(50) NOT NULL, percentage INT NOT NULL, age INT NOT NULL, gender VARCHAR(1) NOT NULL, city INT NOT NULL, PRIMARY KEY (id), FOREIGN KEY (city) REFERENCES City (cid) -- foreign key = save another table value( must have primary key ) in this column -- reference key = other table_name & columan_name (where to match) ); -- FOREIGN KEY (table name) = make city columan as foreign key INSERT INTO tOne(id,name,percentage,age,gender,city) -- manually store the foregin key VALUES (1,"Ram Kumar","45","23","M",1), (2,"Sarita Kumari","56","21","F",2), (3,"Salman Khan","62","20","M",1), (4,"Juhi Chawla","47","18","F",3), (5,"Anil Kapoor","74","22","M",1), (6,"John Abraham","64","21","M",2), (7,"Shahid Kapoor","52","20","M",1);