Primary Keys, Foreign Keys, and Constraints Essentials Quiz Quiz

Explore fundamental concepts of primary keys, foreign keys, and table constraints with this easy quiz designed to enhance your understanding of relational database integrity and structure. Perfect for brushing up on key relational database management topics and ensuring your foundational SQL knowledge is strong.

1. Identifying the Primary Key

   Which constraint ensures that each row in a database table has a unique, non-null identifier, such as a CustomerID in a customer table?

   1. UNIQUE
   2. FOREIGN KEY
   3. PRIMARY KEY
   4. INDEX

   Explanation: A PRIMARY KEY constraint ensures each record in a table is uniquely identifiable and cannot contain NULL values. FOREIGN KEY links two tables but does not guarantee uniqueness in the referencing table. UNIQUE prevents duplicate values but allows NULLs unless specified. INDEX improves performance but does not enforce uniqueness or non-null values.

2. Purpose of a Foreign Key

   What is the main role of a foreign key constraint in a relational database, for example, relating Orders.CustomerID to Customers.CustomerID?

   1. To create a temporary table
   2. To reference data in another table
   3. To store text data only
   4. To ensure uniqueness of values

   Explanation: A foreign key constraint establishes a link between columns in two tables, enforcing referential integrity. It does not ensure uniqueness (that's handled by primary or unique keys). Foreign keys are not restricted by data type like storing text only, nor do they create temporary tables; they're about establishing relationships.

3. Constraint on Unique Values

   Which constraint, when applied to the 'Email' column in a table, ensures that no two records have the same email address?

   1. FOREIGN KEY
   2. CHECK
   3. INDEX
   4. UNIQUE

   Explanation: The UNIQUE constraint prevents duplicate values in a specific column or set of columns. CHECK validates data based on a given condition, INDEX assists in search performance, and FOREIGN KEY relates to enforcing relationships between tables rather than uniqueness within a column.

4. Handling Null Values in Primary Keys

   When defining a primary key on a table, what happens if you try to insert a record with a NULL value in the primary key field?

   1. The insertion fails
   2. A warning is shown but record inserts
   3. The record is inserted with NULL
   4. NULL values are converted to zero

   Explanation: A primary key does not allow NULL values, so any attempt to insert such a record results in a failure. Allowing NULL values or converting them to zero would violate the uniqueness and non-null property. A mere warning without block would undermine data integrity and is not standard SQL behavior.

5. Cascading Deletes

   If a table has a foreign key with an ON DELETE CASCADE action and the referenced row in the parent table is deleted, what happens to related rows in the child table?

   1. No action is taken
   2. Related rows are also deleted
   3. A syntax error occurs
   4. The database is locked

   Explanation: ON DELETE CASCADE automatically removes rows in the child table referencing the deleted parent row, maintaining referential integrity. If 'No action' was specified, nothing would occur; a syntax error is not the outcome of this action, and the table or database does not get locked as a result of this constraint.

6. Types of Keys

   Which of the following key types can a table have more than one of?

   1. FOREIGN KEY
   2. CLUSTERED KEY
   3. PRIMARY KEY
   4. UNIQUE KEY

   Explanation: A table can have multiple UNIQUE keys, ensuring uniqueness across multiple columns or combinations. There can only be one PRIMARY KEY per table. FOREIGN KEYS can be multiple as well, but they establish relationships rather than uniqueness. 'CLUSTERED KEY' is a specific index concept, often singular in practice.

7. Requirement for Foreign Key Columns

   Which of these conditions must be met for a foreign key column to reference another table’s primary key?

   1. Both tables must be temporary
   2. Column names must match exactly
   3. Data types must be the same or compatible
   4. Both keys must be unique keys

   Explanation: For referential integrity, the data types of the foreign key and the referenced key must be compatible, though column names do not need to match. Tables do not have to be temporary, and while primary keys are always unique, the foreign key itself does not have to be unique.

8. Enforcing Data Domain

   Which constraint would you use to ensure only positive values can be entered in an ‘Age’ column of a table?

   1. FOREIGN KEY
   2. DEFAULT
   3. PRIMARY KEY
   4. CHECK

   Explanation: A CHECK constraint can be used to enforce that only certain values, such as positive numbers, are allowed in a column. FOREIGN KEY is for referential integrity, PRIMARY KEY is for uniqueness and identity, and DEFAULT provides an initial value but does not validate user input.

9. Composite Keys

   What term is used when a primary key is composed of more than one column, such as using both 'OrderID' and 'ProductID' in an order details table?

   1. External key
   2. Auto key
   3. Sequential key
   4. Composite key

   Explanation: A composite key is formed from two or more columns to uniquely identify a record. 'Auto key' or 'Sequential key' are not standard terms in relational databases, and 'External key' is not a recognized term for this concept either.

10. Uniqueness of Foreign Keys

    Can a foreign key column in a child table contain duplicate values referencing the same parent row?

    1. Only if primary key is numeric
    2. Yes
    3. Only if indexed
    4. No

    Explanation: A foreign key simply references a parent table's primary or unique key and does not require uniqueness in the child table. Multiple rows in the child can reference the same parent. Whether the column is indexed or the type of the primary key does not affect this rule.