Foreign Key Definition

A Foreign Key is a collection of attributes in one table that refers to the primary key in another table. A foreign key links these two tables. To put it another way, A foreign key is a set of attributes in relational databases that are subject to a specific type of inclusion dependency constraint, precisely a constraint that the tuples which include the attributes of the foreign key in one relation, R must also exist in another (not necessarily distinct) relation, S and furthermore that those attributes must also be a candidate key in S.

A foreign key is a collection of properties that refers to a candidate key. For instance, the MEMBER_NAME attribute of the TEAM table may be a foreign key referring to the PERSON_NAME candidate key in the PERSON table. Any value that represents a member's name in the TEAM table must also represent a person's name in the PERSON table since MEMBER_NAME is a foreign key where in other words, every TEAM member is also a PERSON.

About Foreign key

The table with the foreign key is the child table, while the table containing the candidate key is the preferred or parent table. A candidate key is a set of zero or more attributes whose values are guaranteed to be unique for each tuple (row) in a relation to the database relational modeling and implementation. It is impossible for any other tuple in that relation to have the same value or combination of values as any given tuple's candidate key attributes.

It is typically essential for the foreign key to match the candidate key in some primary table row because the foreign key's primary purpose is to determine a specific row of the referred table. If not, it will have no value (the NULL value). This rule is known as a referential integrity constraint among the two tables. Most database management systems contain procedures to ensure that every non-null foreign key matches to a row of the table in reference since violations of these constraints might result in a variety of database difficulties. Consider a database with two tables i.e. a CUSTOMER table containing all customer data and an ORDER table containing all customer orders. Assume the company requires that each order refers to a single customer. To reflect this in the database, a foreign key column (e.g., CUSTOMERID) is added to the ORDER table which references the primary key of CUSTOMER (e.g., ID). Because a table's primary key must be unique, and CUSTOMERID only contains values from that primary key field, we can assume that CUSTOMERID will identify the specific customer who placed the order when it has a value. Foreign keys play an important role in database design. Ensuring relationships between real-world items are reflected in the database by references and utilizing foreign keys to refer from one table to another is crucial to database design. Database normalization in which tables are split up and foreign keys allow them to be reassembled is another fundamental aspect of database architecture.

Multiple rows in the referencing (or child) table might refer to the same row in the parent table. The relationship between the two tables in this scenario is known as a one-to-many relationship between the referencing table and the referred table.

Furthermore, the child and parent tables may be the same i.e., the foreign key refers to the same table. In SQL:2003, this foreign key type is stated as a self-referencing or recursive foreign key. This is frequently performed in database management systems by linking a first and second reference to the same table. A table can have many foreign keys pointing to a separate parent table. The database system enforces each foreign key independently. As a result, foreign keys can be used to construct cascade relationships between tables. A foreign key is an attribute or combination of attributes in one relation whose values correspond to a primary key in another relation.

Referential Actions in Foreign key

Since the database management system imposes referential restrictions, it must maintain data integrity while deleting (or updating) rows in a referenced table. If dependent rows in referencing tables still exist, their references must be considered. SQL:2003 specifies five separate referential actions that must take place in such cases:

• CASCADE
• RESTRICT
• NO ACTION SET
• NULL SET
• DEFAULT

CASCADE

When rows in the parent (referred) table are deleted (or modified), the rows in the child (referencing) database that have a matching foreign key column are also deleted (or updated). This is known as a cascade delete (or update).

RESTRICT

When a row exists in a referencing or child table that refers the value in the referenced table, the value cannot be updated or deleted. Similarly, the data cannot be deleted if it is referenced by a referencing or child table. To better understand of RESTRICT (and CASCADE), consider the following distinction, which may not be immediately clear. Where the word CASCADE is used, the referential action CASCADE changes the "behaviour" of the (child) table itself. For example, ON DELETE. CASCADE effectively says "When the referenced row is deleted from the other table (master table), then delete also from me." The referential function RESTRICT, on the other hand, affects the "behaviour" of the master table but not the child table, despite the fact that the word RESTRICT occurs in the child table but not the master table! As a result, ON DELETE RESTRICT effectively says: "When someone tries to delete the row from the other table (master table), prevent deletion from that other table". Microsoft SQL 2012 and previous do not support

NO ACTION

NO ACTION and RESTRICT are nearly identical. The primary distinction between NO ACTION and RESTRICT is that the referential integrity check is carried out after attempting to change the table with NO ACTION. RESTRICT performs the check before attempting to execute the UPDATE or DELETE statement. If the referential integrity check fails, both referential actions result in an error in the UPDATE or DELETE statement. In other words, when a UPDATE or DELETE statement is conducted on the referenced table utilising the referential action NO ACTION, the DBMS affirms that none of the referential relationships are violated at the end of the statement execution. Unlike RESTRICT, which assumes that the operation will violate the constraint from the outset, NO ACTION may result in an end state in which no foreign key relationships will be violated by the time the constraint is finally checked, allowing the statement to complete successfully.

SET DEFAULT/ SET NULL

In general, the DBMS action for SET NULL or SET DEFAULT is the same for ON DELETE and ON UPDATE. The value of the relevant referencing attributes is set to NULL for SET NULL and to the given default value for SET DEFAULT.

Importance of Foreign Key

In relational databases, a foreign key is a fundamental notion. It is a key that connects two tables based on a standard column or combination of columns. A foreign key's principal purpose is to protect data integrity by applying referential integrity restrictions between connected tables.

Here are some of the benefits of using foreign keys in a database. The significance of foreign keys.

• Streamline Data Sets

Database administrators can avoid storing duplicate data in multiple tables by using foreign keys. They make data available to several tables without producing duplicate data sets. In other words, they serve as a cross-referencing system between tables

• Promote Efficiency

Primary and foreign keys combine to create a structure in relational databases, allowing users to sort, search, and query data more quickly.

• Ensure Data Integrity

Relationships between primary and foreign keys also contribute to the data integrity of relational databases. They ensure that a value exists in the primary table and the foreign key reference in the primary table remains accurate even if the primary table changes or removed.

• Enforcing Business Rules

Foreign keys can be utilized in the database to enforce business rules. For example, a foreign key can be used to ensure that a customer can only order a product currently in stock.

• Database Maintenance Made Easier

Using foreign keys will make it easier to manage and update the database. For example, if you need to edit a field in one table linked to another, using foreign keys can help ensure that the data is consistent across both tables. Foreign keys are an essential component of relational databases, used to build associations across tables, preserve data consistency, and perform cascading updates and deletes.

Foreign key problems

Many database users experience foreign key errors, which are frequently caused by referential integrity issues. The accuracy and consistency of data in a connection is commonly referred as referential integrity.

A foreign key may point to data that no longer exists, or the data type of the foreign key may not match the data type of the primary key, eroding referential integrity. If the foreign key does not reference all of the data from the primary key, referential integrity is also violated. If a parent table for Sales has a primary key of the company name, department name, and address, then the child table for Customers must refer to all of the parent record's attributes, not just one or two. Referential integrity becomes unstable when a foreign key value in the child table fails to correspond to a value in the parent table. The unmatched value in the child table is known as an orphan record.

Conclusion

Overall, foreign keys are an useful tool for protecting data integrity, boosting query efficiency, simplifying the design of databases, easing data maintenance, and facilitating better data analysis. They are essential for developing durable and dependable database systems. Foreign keys can be used to enforce rules that ensure data accuracy, such as forbidding the deletion of records referenced by other records and ensuring data consistency. Using foreign keys in current relational database management systems is standard practice and crucial for guaranteeing data quality. Foreign keys are utilized in conventional SQL databases and more recent technologies like graph databases. Foreign keys do not distinguish between records in their tables, but they do give a significant value. They allow you build links between tables that share fields.