When working with relationships in Power BI, understanding Primary Keys and Foreign Keys is essential. These keys help connect tables and maintain accurate data relationships in the data model.
What is a Primary Key?
A Primary Key is a column that uniquely identifies each record in a table.
- Each value must be unique
- Duplicate values are not allowed
- Null values are not allowed
Example of Primary Key
Customer Table
| Customer ID | Customer Name |
|---|---|
| C101 | John |
| C102 | Smith |
| C103 | David |
Here, Customer ID is the Primary Key because each customer has a unique ID.
Characteristics of Primary Key
- Contains unique values
- Identifies each row uniquely
- Used to create relationships
- Improves data integrity
What is a Foreign Key?
A Foreign Key is a column in one table that refers to the primary key in another table.
It creates a relationship between two tables.
- Foreign keys can contain duplicate values
- Used to connect fact and dimension tables
Example of Foreign Key
Sales Table
| Sales ID | Customer ID | Sales Amount |
|---|---|---|
| S001 | C101 | 500 |
| S002 | C102 | 700 |
| S003 | C101 | 300 |
Here, Customer ID is the Foreign Key because it references the Customer ID in the Customer table.
How Primary and Foreign Keys Work Together
Customer Table (Primary Key)↓Sales Table (Foreign Key)
- The primary key uniquely identifies records
- The foreign key connects related tables
This relationship helps Power BI filter and analyze data correctly.
Why Keys are Important in Power BI
Primary and foreign keys are important because they:
- Create relationships between tables
- Improve data modeling
- Enable accurate filtering
- Support efficient reporting and analysis
Difference Between Primary Key and Foreign Key
| Feature | Primary Key | Foreign Key |
|---|---|---|
| Purpose | Uniquely identifies records | Connects tables |
| Uniqueness | Must contain unique values | Can contain duplicate values |
| Null Values | Not allowed | Allowed |
| Table Role | Parent table | Child table |
| Relationship | Referenced by foreign keys | References primary key |
Best Practices
- Use unique columns as primary keys
- Avoid duplicate values in primary keys
- Ensure matching data types between keys
- Maintain clean and consistent relationships
A strong understanding of keys helps in building clean, reliable, and efficient Power BI data models.
