MySQL

A foreign key is a column or a set of columns in a table that establishes a link between the data in two tables. It refers to the primary key in another table, creating a relationship between the two tables. Foreign keys are crucial for maintaining referential integrity, ensuring that relationships between tables remain consistent and that data is accurate and reliable.

Here’s a detailed look at foreign keys:

1. Definition and Purpose

   • A foreign key is used to link records from one table to records in another table.
   • It helps maintain data integrity by ensuring that the value in the foreign key column matches a value in the primary key column of the referenced table.

   CREATE TABLE Departments (
       DepartmentID INT PRIMARY KEY,
       DepartmentName VARCHAR(50)
   );
   
   CREATE TABLE Employees (
       EmployeeID INT PRIMARY KEY,
       FirstName VARCHAR(50),
       LastName VARCHAR(50),
       DepartmentID INT,
       FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
   );

2. Referential Integrity

   • Foreign keys enforce referential integrity by ensuring that only valid data can be entered into the foreign key column.
   • If a record is deleted or updated in the referenced table, the changes can be propagated to the table with the foreign key.

   -- Deleting a department with cascading updates
   DELETE FROM Departments WHERE DepartmentID = 101;

   -- Example of ON DELETE CASCADE
   CREATE TABLE Employees (
       EmployeeID INT PRIMARY KEY,
       FirstName VARCHAR(50),
       LastName VARCHAR(50),
       DepartmentID INT,
       FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) ON DELETE CASCADE
   );

3. Inserting Data

   • When inserting data into a table with a foreign key, the value must exist in the referenced table.

   -- Inserting a valid record
   INSERT INTO Departments (DepartmentID, DepartmentName) VALUES (101, 'Sales');
   INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID) VALUES (1, 'John', 'Doe', 101);
   
   -- Attempt to insert a record with an invalid DepartmentID will fail
   INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID) VALUES (2, 'Jane', 'Doe', 999);

4. Updating Data

   • Updates to the foreign key column must also be valid with respect to the referenced table.

   -- Updating DepartmentID in Employees table
   UPDATE Employees SET DepartmentID = 102 WHERE EmployeeID = 1;

5. Deleting Data

   • When a record in the referenced table is deleted, the foreign key constraints can determine whether related records should be deleted or updated.

   -- Example of a foreign key with ON DELETE SET NULL
   CREATE TABLE Employees (
       EmployeeID INT PRIMARY KEY,
       FirstName VARCHAR(50),
       LastName VARCHAR(50),
       DepartmentID INT,
       FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID) ON DELETE SET NULL
   );
   
   -- Deleting a department will set DepartmentID to NULL for employees in that department
   DELETE FROM Departments WHERE DepartmentID = 101;

6. Enforcing Constraints

   • Foreign keys can be defined with constraints that specify actions on delete or update, such as CASCADE, SET NULL, or NO ACTION.

   CREATE TABLE Employees (
       EmployeeID INT PRIMARY KEY,
       FirstName VARCHAR(50),
       LastName VARCHAR(50),
       DepartmentID INT,
       FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
       ON DELETE SET NULL
       ON UPDATE CASCADE
   );

Foreign keys are essential for establishing relationships between tables and ensuring the accuracy and consistency of your database. They help in maintaining the integrity of data across related tables, making your database more reliable and robust.

To gain complete access, login with gmail or outlook, no need of signup, click here

Understanding Foreign Keys in Database Design

A foreign key in database design is a column (or set of columns) in one table that uniquely identifies a row of another table. It's a key used to link two tables together. This concept might seem a bit abstract, so let's use an analogy to simplify it, followed by a basic code example to illustrate how it works in practice.

Analogy: Cities and Countries

Imagine a world map with countries and their cities. Each country can have multiple cities. To represent this relationship in a database, you would have two tables: aCountriestable and aCitiestable.

• TheCountriestable has a primary key calledCountryIDthat uniquely identifies each country.
• TheCities table lists cities around the world, and each city is associated with a country.

In this analogy, theCountryIDfield in theCities table acts as a foreign key. It references theCountryID primary key in theCountries table. This setup ensures that each city in theCities table is linked to a specific country in theCountries table. Just as you would use a country's name to find its cities on a map, in a database, you use the foreign key to retrieve all cities belonging to a particular country.

Code Example

Let's translate this analogy into a simple SQL code example to demonstrate how foreign keys work in practice.

SQL Table Creation

-- Create Countries table
CREATE TABLE Countries (
    CountryID int NOT NULL,
    CountryName varchar(255) NOT NULL,
    PRIMARY KEY (CountryID)
);

-- Create Cities table
CREATE TABLE Cities (
    CityID int NOT NULL,
    CityName varchar(255) NOT NULL,
    CountryID int,
    PRIMARY KEY (CityID),
    FOREIGN KEY (CountryID) REFERENCES 
Countries(CountryID)
);

In this example:

• TheCountriestable is created withCountryIDas its primary key.
• TheCitiestable is created withCityIDas its primary key andCountryIDas a foreign key.
• TheFOREIGN KEY (CountryID) REFERENCES Countries(CountryID)line in theCitiestable definition establishesCountryIDas a foreign key that references theCountryIDprimary key in theCountriestable.

This setup allows the database to understand the relationship between countries and their cities. For instance, if you have a country withCountryID = 1, and you want to add cities to this country in theCitiestable, you would setCountryID = 1for these cities. The database then knows these cities belong to the country withCountryID = 1.

Conclusion

Foreign keys serve as crucial links between tables in relational databases, enabling the representation of real-world relationships like the one between countries and cities. They ensure data integrity by only allowing the insertion of records that have a corresponding value in the linked table. Through foreign keys, databases can maintain accurate and consistent relationships between data points across different tables.

PRODUCT TYPE ID AS FOREIGN KEY IN PRODUCT TABLE

ORDER ID AS FOREIGN KEY IN ORDER DETAIL TABLE

DEPARTMENT ID AS FOREIGN KEY IN EMPLOYEE TABLE