DEV Community

Gurpinder Singh
Gurpinder Singh

Posted on

Why are foreign keys and primary keys used in databases?

In MySQL and other relational database management systems (RDBMS), foreign keys and primary keys serve crucial roles in maintaining data integrity and establishing relationships between tables. Here's how they work:

How to Set a Foreign key

What is the Difference between in Index and Foreign Key?

Primary Key: A primary key uniquely identifies each record in a table. It ensures that each row in a table is uniquely identifiable and cannot contain duplicate values. In MySQL, a primary key is typically created using the PRIMARY KEY constraint when defining a table. For example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50),
    ...
);

Enter fullscreen mode Exit fullscreen mode

In this example, EmployeeID is the primary key of the Employees table.

Foreign Key: A foreign key establishes a link or relationship between two tables. It ensures referential integrity by enforcing a relationship between the data in the referencing table (child table) and the referenced table (parent table). The values in the foreign key column(s) of the child table must match the values in the primary key column(s) of the parent table. In MySQL, a foreign key is created using the FOREIGN KEY constraint. For example:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    ...
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Enter fullscreen mode Exit fullscreen mode

In this example, CustomerID in the Orders table is a foreign key that references the CustomerID primary key in the Customers table.

By using primary keys and foreign keys, you can create relationships between tables, enforce data integrity, and ensure consistency in your database. This allows for efficient data retrieval and manipulation, as well as maintaining the integrity and reliability of your data.

Thanks for reading,
Dgi Host.com

Top comments (0)