Foreign Key in DBMS

Foreign keys are a set of constraints in DBMS that establish relationships between tables and also ensure consistency and integrity of data.

• A foreign key is applied to a column (or attribute) of one table which references the primary key of a column in another table.
• It act as a cross-reference between two tables. It helps to maintain data integrity which is known as "Referential Integrity Constraints".
• A foreign key ensures that the data in one table corresponds to a valid entry (or entries) in another table.
• A foreign key can refer to a column in the same table, which is called self-referencing. This is often used to show relationships within the same table, like a manager and their employees, members of a family tree, or folders inside other folders.

Syntax For Creating and Deleting Foreign Key

Let’s see the Foreign Key syntax used for creating a table.

Syntax for Creating Foreign Key:

CREATE TABLE Child_table_name (

Child_Column1 data_type,

Child_Column2 data_type, ...,

FOREIGN KEY (Child_foreign_key_column)

REFERENCES referenced_table_name (referenced_primary_key_column) );

Below syntax is used to delete the foreign key in DBMS.

Syntax for Dropping Foreign Key:

ALTER TABLE Child_table_name

DROP FOREIGN KEY Child_foreign_key_name;

Example

• dept_id is primary key in department_info table.
• dept_id is foreign key in employee_info table.

It means that dept_id field of employee_info table can have only those value which are present in dept_id field of department_info table.

• dept_id field in department_info table is for unique identification of the records in the table.
• dept_id field in employee_info table is for knowing which employee is working on which department.

Need of Foreign Keys in DBMS

Foreign keys plays a major role in database management systems (DBMS) for the following reasons:

• Data Integrity: We need foreign keys as they help us making sure that data is consistent, complete, between both the tables and overall accuracy is maintained.
• Query Optimization: Foreign keys optimizes the query execution by utilizing query plans more efficiently and improving the relationships between tables. It also helps in fast data retrieval.
• Establishing Relationships: The main requirement of foreign keys is the establishment of relationships between tables. It makes sure that data is linked across multiple tables and helps in storing and retrieving data.
• Data Security: Foreign keys helps in improving the security of data by preventing unauthorized modifications or deletions of important data in the referenced table.
• Database Maintenance: Foreign keys are required in database maintenance tasks and help to ensure integrity and consistency of data during these operations.

To Read more about SQL FOREIGN KEY Constraint Refer, Here.