Database Keys: Primary and Foreign Key Concepts and Relationships

What is a foreign key?

A foreign key is a field (or collection of fields) in one table that references a field (or collection of fields) in another table.

What is a primary key?

A primary key is a field (or collection of fields) in a table that uniquely identifies each record.

What does a foreign key ensure in a database?

It ensures that for every value in the child table, there is a corresponding value in the parent table.

What does the notation '∞' represent in database diagrams?

It indicates a foreign key relationship between the child and parent tables.

What are the implications of having NULL values in a foreign key?

It indicates that the record in the child table does not refer to any record in the parent table.

What does a many-to-one relationship imply in the context of foreign keys?

One record in the parent table may be referenced by many records in the child table.

What is an example of a foreign key relationship?

Orders.CustomerID references Customers.CustomerID, indicating which customer made the order.

What is the role of the Orders table in the context of foreign keys?

The Orders table acts as a child table that references the parent Customers table through the CustomerID foreign key.

What is the significance of the child table in a foreign key relationship?

The child table contains the foreign key that references the parent table.

What is the relationship between the child table and the parent table in a foreign key relationship?

The child table references the parent table, indicating a many-to-one relationship.

What is the purpose of foreign keys in database design?

They maintain relationships between tables and ensure data integrity.

What must be true about the values in the referenced field of a foreign key?

Values in the referenced field(s) must be unique.

Can a foreign key have NULL values?

Yes, a foreign key can have NULL values, indicating no reference to the parent table.

What is the difference between a foreign key and a primary key?

A primary key uniquely identifies records in its own table, while a foreign key references a primary key in another table.

What should you consider when designing foreign key relationships?

Ensure that the referenced fields are unique and that referential integrity is maintained.

How can foreign keys be created in SQLite Studio?

Foreign keys can be created by selecting the appropriate field and indicating it as a foreign key in the dialog.

What are the two main properties of a primary key?

1. Each record has a unique value. 2. No record has a NULL value.

What happens when a value is removed from the parent table?

All corresponding values for that key must be removed from the child table to maintain referential integrity.

How is a primary key indicated in database diagrams?

Primary keys are indicated with boldface.

What is referential integrity?

Referential integrity ensures that a value for a foreign key in the child table must exist in the parent table.

What is an example of a foreign key referencing another table?

ShipVia in the Orders table is a foreign key referencing ShipperID in the Shippers table.

What is the role of the Customers table in the context of foreign keys?

The Customers table acts as a parent table that is referenced by the child Orders table.