Lesson 07 (Primary Key & Foreign Key)

 

Primary Key/Foreign Key

Primary Key

Combinagtion of column that uniquely identifies a row in table.

Rules for Primary Key

1. Primary key must contain unique value.

2. Primary key can’t be null.

3. There is only one primary key in table.

Syntax-

Col_name data_type PRIMARY KEY.

like roll no in every class.

We can define more than one column for primary key.

PRIMARY KEY(COL1, COL2,......)

PRIMARY KEY in existing table

ALTER table(table_name) add PRIMARY KEY(primary key column)

use college;

create table emp11(empid int PRIMARY KEY, ename varchar(10));

describe emp11;

create table emp12 (empid int, ename varchar(10), 

→ PRIMARY KEY(empid, ename);

now you can see by describe command

create table emp13 (empid int, ename varchar(10));

alter table emp13

→ add PRIMARY KEY(empid);

FOREIGN KEY

It is a field in a table that matches another field of another table. It is used to maintain referential integrity. A table may have more than one foreign key and each fireign key in the child table may refer to a different parant table.

Syntax-

foreign key(col-name) references <parant_table> (col_name)

create table e1(eid int PRIMARY KEY) engine=InnoDB;

create table d1(did int, FOREIGN KEY (did) references e1(eid)) engine=InnoDB;

अब जब भी हम did में कोई डाटा भरेंगे तो check करेगा की e1 की eid में वो डाटा होना आवश्यक है |

for example

insert into table e1 values(1);

insert into table e1 values(2);

insert into table d1 value (1);

insert into table d1 values(3); error दिखायेगा क्योंकि e1 की eid में हमने value 3 नहीं भरी है |