0
2.0kviews
Define foreign key.
2 Answers
0
17views

FOREIGN KEY:

  • Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table.
  • It acts as a cross-reference between tables because it references the primary key of another table, thereby establishing a link between them.
  • For example, a table called Player has a primary key called Player_ID. Another table called PlayerDetails has a foreign key which references Player_ID in order to uniquely identify the relationship between both the tables.
  • A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
  • FOREIGN KEY Constraint on CREATE TABLE
  • The following SQL creates a FOREIGN KEY on the "P_Id" column when the "Orders" table is created. CREATE TABLE Orders

    (O_Id int PRIMARY KEY,

OrderNo int NOT NULL,

P_Id int REFERENCES Persons (P_Id));

The "Persons" table:

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20

The "Orders" table:

O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 2
4 24562 1
0
3views

Foreign Key:

  • Primary key of one relation is used as an attribute in another relation is called as foreign key.

  • It is a part of referential integrity where one relation is referenced in another relation.

  • Consider two relations as:

emp(empNo, EN, FN, email)

Dept(DeptNo, Name, Location)

  • To establish a relation between emp and Dept, one may have to refer the primary key of Dept in emp.

  • So now new relation can be written as:

emp (empNo, FN, LN, email, DNo)

  • Here 'DNo' is a foreign key.

  • This referential integrity can be shown in a diagramatic way as:

enter image description here

  • Foreign key also helps in retrieving related information from two related tables.

  • For eg. one may retrieve the department of any employee using foreign ey 'Dno'

Please log in to add an answer.