Рет қаралды 11,656
In this video, I explain the primary key and foreign in a database setting as covered on the CPA exam.
✔️Accounting students and CPA Exam candidates, check my website for additional resources: farhatlectures.com/
#cpaexam #cpaexaminindia #cpaevolution
Primary keys and foreign keys are essential concepts in relational database design. They are used to establish and enforce relationships between tables, ensuring data integrity.
1. Primary Key (PK)
- Purpose: Uniquely identifies each record in a table.
- Characteristics**:
Must contain a unique value for each row of data.
Cannot contain `NULL` values.
A table can have only one primary key, which may consist of single or multiple columns.
It provides a way to ensure data in the specific column(s) is unique.
- Use case: If you have a `Users` table, each user might have a unique `UserID` which acts as a primary key.
- Physical Implementation: Often used as the basis for creating indexes, which helps speed up queries.
2. Foreign Key (FK)
- Purpose**: Used to establish and enforce a link between the data in two tables.
- Characteristics:
A foreign key in one table points to a primary key in another table.
Can contain duplicate values.
Can contain `NULL` values if the relation is optional.
A table can have multiple foreign keys depending on its relationships with other tables.
It ensures referential integrity in the relation between two tables. If there is a foreign key defined, actions that would break references are typically rejected by the database.
- Use case: In an `Orders` table, there might be a `UserID` column as a foreign key linking each order to a user in the `Users` table.
- Physical Implementation: When defined, typically creates a database constraint, which can enforce behavior like cascade delete or update.
An Example to Illustrate:
Imagine a library database. There are two tables: `Books` and `Authors`.
- The `Authors` table might have:
- AuthorID (Primary Key)
- AuthorName
- DateOfBirth
- The `Books` table could have:
- BookID (Primary Key)
- Title
- AuthorID (Foreign Key pointing to AuthorID in the Authors table)
Here, the `AuthorID` in the `Authors` table is the primary key, ensuring each author has a unique identifier. The same `AuthorID` in the `Books` table acts as a foreign key, connecting each book to its author. Through this design, if you wanted to find out who wrote a particular book, you'd use the foreign key relationship between the `Books` table and the `Authors` table.
In summary, primary keys ensure uniqueness within their own table, while foreign keys create and enforce a relationship between two tables.