CPA Exam Simulation: Relational database Primary and Foreign Key

In this video, I explain the primary and foreign key in a relational database covered in a CPA exam simulation.

✔️Accounting students and CPA Exam candidates, check my website for additional resources: https://farhatlectures.com/
#cpaexaminindia #cpaexamevloution #cpaexam

Primary Key:
A primary key is a unique identifier for a record in a relational database table. It ensures that each row in the table has a distinct and non-null value, which serves as a means to uniquely identify that row. The primary key enforces data integrity by preventing duplicate or null values in the designated column(s). It is crucial for maintaining the uniqueness and integrity of the data in a table.

Foreign Key:
A foreign key is a field in a database table that establishes a link between the data in two tables. It creates a relationship between these tables, allowing them to share and reference information. The foreign key in one table corresponds to the primary key in another table, typically creating a parent-child relationship. This relationship enables efficient data retrieval and ensures referential integrity, preventing the insertion of data that doesn't have a corresponding entry in the related table.

Example:
Let's consider a simple example with two tables: "Customers" and "Orders."

1. *Customers Table:*
Columns: CustomerID (Primary Key), Name, Email, Phone

2. *Orders Table:*
Columns: OrderID (Primary Key), CustomerID (Foreign Key), OrderDate, TotalAmount

In this scenario, the "Customers" table has a primary key "CustomerID," which uniquely identifies each customer. The "Orders" table has a primary key "OrderID," which uniquely identifies each order, and a foreign key "CustomerID," which establishes a relationship with the "Customers" table.

The primary key "CustomerID" in the "Customers" table ensures that each customer has a unique identifier. This prevents duplicate customers from being added to the table and maintains data integrity.

The foreign key "CustomerID" in the "Orders" table references the "CustomerID" in the "Customers" table. This establishes a link between orders and customers, indicating which customer placed a particular order. It ensures that an order cannot be created without a corresponding customer entry in the "Customers" table, maintaining referential integrity.

For instance, if we have the following data:

*Customers Table:*
| CustomerID | Name | Email | Phone |
|------------|------------|--------------------|---------------|
| 1 | John Smith | [email protected] | 123-456-7890 |
| 2 | Jane Doe | [email protected] | 987-654-3210 |

*Orders Table:*
| OrderID | CustomerID | OrderDate | TotalAmount |
|---------|------------|-------------|-------------|
| 101 | 1 | 2023-08-15 | $100 |
| 102 | 2 | 2023-08-16 | $75 |

In this example, the foreign key "CustomerID" in the "Orders" table references the primary key "CustomerID" in the "Customers" table. The order with ID 101 is linked to the customer with ID 1 (John Smith), and the order with ID 102 is linked to the customer with ID 2 (Jane Doe). This relationship allows for efficient retrieval of customer-related information when querying order data and maintains data consistency between the two tables.