Database Keys and Referential Integrity

Questions and Answers

What is a primary key?

A column that can be left empty.

Any column in a table.

A field that can contain duplicate values.

A unique identifier for a table. (correct)

A candidate key is always a primary key.

False

What is a composite key?

A key defined by more than one column.

A __________ key is a field linked to another table's primary key field.

foreign Signup and view all the answers

What does referential integrity ensure?

Data remains accurate and consistent. Signup and view all the answers

Match the following terms with their definitions:

Primary Key = Unique identifier for a table Foreign Key = Field linked to a primary key in another table Referential Integrity = Consistency of data across related tables Composite Key = Key defined by multiple columns Signup and view all the answers

Referential integrity allows the deletion of a primary key if there are associated foreign keys in other tables.

False Signup and view all the answers

Provide an example of a candidate key.

Unique email address Signup and view all the answers

Study Notes

Primary Key

A primary key is a unique identifier for a table, consisting of one or more columns.
Candidate keys can guarantee uniqueness but are not necessarily primary keys until specified.
Example: A user table may have a unique userid as the primary key and a unique email address as a candidate key that is not designated as the primary key.

Composite Key

A composite key consists of multiple columns used to define a primary key.

Foreign Key

A foreign key is a column that creates a link to another table's primary key, establishing a relationship between two tables.

Referential Integrity

Referential integrity ensures the accuracy and consistency of data across related tables.
This is maintained by ensuring that foreign key values reference valid and existing primary key values in the parent table.
Example: In the Chinook database, each Customer's SupportRepId is a foreign key linked to the EmployeeId in the Employees table.
Deleting or changing an EmployeeId without corresponding updates to SupportRepId would compromise database integrity, potentially leading to Customers with invalid or missing SupportRepId entries.

Description

This quiz covers key concepts related to primary keys, composite keys, foreign keys, and the principle of referential integrity in relational databases. Understanding these elements is crucial for maintaining data integrity and establishing relationships between tables in a database. Test your knowledge and ensure your comprehension of these fundamental aspects of database management.