2 Keys and Relationships Free Online FlashCards

What is a candidate key?

A candidate key is a minimal set of one or more columns that uniquely identifies every row.

What is a table’s primary key?

A primary key is the candidate key selected as the table’s main identifier. It must be unique and non-null, and a table can have at most one.

What is a composite key?

A composite key uses multiple columns together as a key.

How can a table enforce a candidate key that is not its primary key?

Other candidate keys can be enforced with UNIQUE constraints.

What key can a foreign key refer to?

A foreign key refers to a primary key or another unique key in a different table.

What does referential integrity require?

Referential integrity requires each non-null foreign-key value to match an existing value in the referenced key.

What must match between foreign-key columns and referenced columns?

The referencing and referenced columns must have the same number of columns and compatible data types.

How does foreign-key nullability represent an optional relationship?

A nullable foreign key allows an optional relationship. Add NOT NULL when every row must be linked.

How does a foreign key represent a one-to-many relationship?

In a one-to-many relationship, one parent can be linked to many child rows, while each child points to one parent.

How can a foreign key enforce a one-to-one relationship?

Make the foreign key UNIQUE so that no two rows can refer to the same parent.

How is a many-to-many relationship usually represented?

A many-to-many relationship is usually represented by a junction table with foreign keys to both related tables.

How can an enrollment table prevent duplicate student-course pairs?

A composite primary key on the two foreign keys can prevent duplicate pairs, so the same student-course enrollment cannot be entered twice.