Views, Indexing, Primary & Foreign Keys
In relational databases, proper use of keys, views, and indexes ensures data integrity, performance, and usability. Understanding these concepts is essential for designing robust databases and writing efficient SQL queries.
1. Primary Keys
A Primary Key uniquely identifies each record in a table. It ensures that:
- No two rows have the same key value
- The key column(s) cannot be NULL
Why it’s important:
- Maintains uniqueness of records
- Helps in referencing rows from other tables
2. Foreign Keys
A Foreign Key is a column in one table that refers to the primary key of another table. It creates a relationship between tables.
Why it’s important:
- Maintains referential integrity
- Ensures only valid values are entered in related tables
3. Views
A View is a virtual table created from a SELECT query. It does not store data but shows a snapshot of data from one or more tables.
Why it’s useful:
- Simplifies complex queries
- Provides security by restricting access to specific columns or rows
- Helps create read-only versions of tables
4. Indexing
An Index is a database structure that improves the speed of data retrieval. It works like an index in a book—making it faster to locate specific rows.
Why it’s important:
- Boosts performance of SELECT queries with WHERE, JOIN, or ORDER BY
- Reduces the need to scan the entire table
Types of Indexes:
- Single-column index: On one column
- Composite index: On two or more columns
- Unique index: Ensures all values in the column(s) are unique
Summary Table
| Concept | Function | Example Use |
|---|---|---|
| Primary Key | Uniquely identifies rows | emp_id in Employees |
| Foreign Key | Links rows across tables | dept_id in Employees |
| View | Virtual table for simplified queries | employee_summary view |
| Index | Improves query performance | Index on name column |
