Question

What is a Foreign Key?

Answer 1

A FOREIGN KEY comprises of single or collection of fields in a table that essentially refer to the PRIMARY KEY in another table. Foreign key constraint ensures referential integrity in the relation between two tables.

The table with the foreign key constraint is labelled as the child table, and the table containing the candidate key is labelled as the referenced or parent table.

CREATE TABLE Students ( /* Create table with foreign key - Way 1 */

    ID INT NOT NULL

    Name VARCHAR(255)

    LibraryID INT

    PRIMARY KEY (ID)

    FOREIGN KEY (Library_ID) REFERENCES Library(LibraryID)

);

CREATE TABLE Students ( /* Create table with foreign key - Way 2 */

    ID INT NOT NULL PRIMARY KEY

    Name VARCHAR(255)

    LibraryID INT FOREIGN KEY (Library_ID) REFERENCES Library(LibraryID)

);

ALTER TABLE Students /* Add a new foreign key */

ADD FOREIGN KEY (LibraryID)

REFERENCES Library (LibraryID);

Q   =>   What type of integrity constraint does the foreign key ensure?

Q   =>   Write a SQL statement to add a FOREIGN KEY 'col_fk' in 'table_y' that references 'col_pk' in 'table_x'.

Answer 2

The foreign key is used to link one or more tables together. It is also known as the referencing key. A foreign key is specified as a key that is related to the primary key of another table. It means a foreign key field in one table refers to the primary key field of the other table. It identifies each row of another table uniquely that maintains the referential integrity. The primary key-foreign key relationship is a very crucial relationship as it maintains the ACID properties of the database sometimes. It also prevents actions that would destroy links between the child and parent tables.

We can define a foreign key into a table as follows:

CONSTRAINT constraint_name]    

    FOREIGN KEY [foreign_key_name] (col_name, ...)    

    REFERENCES parent_tbl_name (col_name,...)    

To read more information, click here.