Primary and foreign keys, normalization forms, indexes, and constraints, each explained with a short example.
35 cards · basic cards · AI-written, checked twice. Edit anything.
- What is a primary key in a database table?
Show answer
A column or group of columns that uniquely identifies each row in a table. No two rows can have the same primary key value.- Why do we use primary keys?
Show answer
To ensure each row is uniquely identifiable, prevent duplicate records, and serve as the foundation for relationships between tables.- What is the difference between a primary key and a unique constraint?
Show answer
A primary key uniquely identifies rows and cannot be null; a unique constraint enforces uniqueness but allows null values (except in some systems) and a table can have only one primary key but multiple unique constraints.- What is a foreign key?
Show answer
A column or group of columns in one table that references the primary key of another table, creating a link between the two tables.- What is the purpose of a foreign key?
Show answer
To maintain referential integrity by ensuring that values in the foreign key column exist as primary keys in the referenced table, preventing orphaned records.- What is referential integrity?
Show answer
A database constraint that ensures every foreign key value either exists as a primary key in the referenced table or is null.- What is a database index?
Show answer
A data structure that speeds up retrieval of rows from a table by maintaining a sorted copy of selected columns.- What are the benefits of creating an index?
Show answer
Faster data retrieval for queries that filter or sort on indexed columns, improving SELECT query performance.- What is the trade-off of creating an index?
Show answer
Indexes consume extra disk space and slow down INSERT, UPDATE, and DELETE operations because the index must also be updated.- What is a clustered index?
Show answer
An index that determines the physical order of rows in the table. A table has only one clustered index, typically the primary key.- What is a non-clustered index?
Show answer
An index that does not affect the physical order of rows and points back to the actual row location. A table can have multiple non-clustered indexes.- What is database normalization?
Show answer
The process of organizing table structure to eliminate redundancy and dependency anomalies, typically following progressive normal forms (1NF, 2NF, 3NF, BCNF).- What does First Normal Form (1NF) require?
Show answer
All column values must be atomic (indivisible), meaning each cell contains only a single value, not lists or repeating groups.- Describe a violation of 1NF and how to fix it.
Show answer
A cell containing comma-separated phone numbers like '555-1234, 555-5678' violates 1NF. Fix by creating a separate Phones table with one phone per row, linked by customer ID.- What does Second Normal Form (2NF) require?
Show answer
The table must be in 1NF and every non-key column must depend on the entire primary key, not just part of it.
FlashKeepers