PRIMARY KEY AND FOREIGN KEY

                 CREATING A TABLES

CHARACTERISTICS OF PRIMARY KEY

1. Primary key having a unique data set.

2. Primary key does not have a null value.

3. A Table can contain only one primary key constraint.

CREATE TABLE districts(

    cid INT NOT NULL AUTO_INCREMENT,

    district_name VARCHAR(50) NOT NULL,

    PRIMARY KEY (cid)

);

  INSERTING THE RECORD IN DISTRICTS  TABLES

INSERT INTO districts(district_name)

VALUES('Agra'),

('Delhi'),

('Bhopal'),

('Jaipur'),

('Noida'),

('Lucknow'),

('ballia');

select * from districts 

OUTPUT OF DISTRICTS TABLES

1Agra
2Delhi
3Bhopal
4Jaipur
5Noida
6Lucknow
7ballia

1. A foreign key is a key used to join the two tables.

2. A Foreign key is one table used to point primary key in other tables


                       CREATING A TABLES CITIZENS



CREATE TABLE citizens(

id INT NOT NULL,

name VARCHAR(50) NOT NULL,

percentage_of_population INT NOT NULL,

age INT NOT NULL,

gender VARCHAR(1) NOT NULL,

district_name INT NOT NULL,

PRIMARY KEY (id),

    foreign key (district_name) REFERENCES district_name(cid)

    );


  INSERTING THE RECORD INTO CITIZENS TABLES AND

 SET THE PRIMARY AND FOREIGN KEY


INSERT INTO citizens(id,name,percentage_of_population,age,gender,district_name)

VALUES

(1,"Ramesh Kumar","45","13","M",1),

(2,"Saroja Kumari","56","21","F",2),

(3,"sarsvari devi ","62","20","M",1),

(4,"Juhi kumari","47","18","F",3),

(5,"Anil yadav","74","22","M",1),

(6,"Joy Shanker","64","21","M",2),

(7,"sunil yadav ","52","20","M",1);


select * from citizens

OUTPUT OF CITIZENS TABLES

Ramesh Kumar14513M1
Saroja Kumari25621F2
sarsvari devi 36220M1
Juhi kumari44718F3
Anil yadav57422M1
Joy shanker66421M2
sunil yadav 75220M1

 if already existing tables having then syntax are

 ALTER TABLE TABLE_NAME
ADD FOREIGN KEY (DISTRICT_NAME) REFERENCES DISTRICTS_NAME(cid);

Post a Comment

0 Comments