SQLite Essentials: Keys and Constraints Quiz Quiz

Explore key concepts of primary keys, foreign keys, and constraints in SQLite with this quiz. Strengthen your foundational SQL knowledge and understand how data integrity is maintained using unique identifiers and relational links.

1. Primary Key Basics

   What is the main purpose of declaring a PRIMARY KEY on a column in an SQLite table?

   1. To ensure all values in the column are unique and not null
   2. To store large text values in the column
   3. To allow duplicate values in the column
   4. To prevent updating the column values

   Explanation: A PRIMARY KEY uniquely identifies each row and requires every value to be unique and non-null. It does not allow duplicate or null values, ensuring data integrity. Allowing duplicate values would defeat the purpose of a primary key, and it is unrelated to storing large text values or preventing updates, which are handled differently.

2. Composite Primary Key

   When using a composite primary key in SQLite, which of the following is true?

   1. Only one of the columns can have null values
   2. It creates an index for only one column
   3. The combination of specified columns must be unique for each row
   4. Each column must be unique individually

   Explanation: A composite primary key ensures that the combined values of the specified columns are unique for each row, not the individual columns. Each column does not need to be unique separately, and typically neither can have null values. An index is created for the combination, not for only one column.

3. Foreign Key Relationships

   Which keyword is used in SQLite to create a foreign key relationship between two tables?

   1. OUTER KEY
   2. SIDE KEY
   3. FOREIGN KEY
   4. DIFFERENT KEY

   Explanation: The FOREIGN KEY keyword defines a relationship between columns in two tables, referencing the primary key in the related table. OUTER KEY, DIFFERENT KEY, and SIDE KEY have no meaning in this context and are not valid SQLite keywords.

4. Enforcing Referential Integrity

   What happens if you try to insert a value into a child table's foreign key column that does not exist in the referenced parent table in SQLite?

   1. The row is inserted with the foreign key set to null
   2. The value is automatically added to the parent table
   3. The insert fails and returns an error
   4. The value is ignored

   Explanation: If a foreign key value does not match an existing value in the parent table, SQLite enforces referential integrity and prevents the insertion, returning an error. Values are not added automatically, they are not ignored, and the row is not inserted with a null foreign key unless null is allowed and no constraint is violated.

5. UNIQUE Constraint Application

   In SQLite, which constraint would you apply to a column so that all its values must be different from each other, but null values are allowed?

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

   Explanation: The UNIQUE constraint allows any number of null values but enforces all non-null values to be distinct. PRIMARY KEY also enforces uniqueness but does not allow nulls, while FOREIGN KEY defines relationships, not uniqueness. DEFAULT sets a value and does not enforce uniqueness.

6. NOT NULL Constraint Role

   What is the effect of adding a NOT NULL constraint to an SQLite column?

   1. It allows only null values in the column
   2. It prevents null values from being inserted into the column
   3. It forces the column to become a primary key
   4. It disables unique value enforcement

   Explanation: Adding a NOT NULL constraint ensures that null values cannot be entered in the column, maintaining data completeness. It does not make the column a primary key or interfere with uniqueness. Enabling only null values would contradict the NOT NULL constraint.

7. ON DELETE Actions

   If a foreign key in SQLite is defined with ON DELETE CASCADE, what happens when a referenced row in the parent table is deleted?

   1. Deletion is not allowed
   2. Foreign key constraints are dropped
   3. All related rows in the child table are also deleted
   4. Related child rows are set to null

   Explanation: The ON DELETE CASCADE action ensures that deleting a parent row will automatically delete all related child rows. It does not drop foreign key constraints or simply prevent deletion. Setting related rows to null is accomplished using ON DELETE SET NULL.

8. Default Values

   Which SQLite constraint can be used to set a predefined value if no value is provided for a column during insertion?

   1. AUTO
   2. PRIMARY KEY
   3. UNIQUE
   4. DEFAULT

   Explanation: The DEFAULT constraint assigns a specified value to the column when none is provided during the insert. PRIMARY KEY and UNIQUE control row uniqueness, and AUTO does not exist as a constraint in SQLite.

9. Auto-Incrementing Keys

   How can you make an integer primary key in SQLite automatically get a unique value for each new row?

   1. By declaring it as INTEGER PRIMARY KEY AUTOINCREMENT
   2. By declaring it as TEXT PRIMARY KEY
   3. By using CHAR PRIMARY KEY
   4. By adding a UNIQUE constraint only

   Explanation: INTEGER PRIMARY KEY AUTOINCREMENT ensures each new row automatically receives the next available integer value. CHAR PRIMARY KEY and TEXT PRIMARY KEY require manual value entry. The UNIQUE constraint does not provide auto-incrementing functionality.

10. Checking Constraint Violation

    If you try to insert duplicate values into a column with a UNIQUE constraint in SQLite, what will be the outcome?

    1. The column becomes a primary key
    2. Duplicates are silently deleted
    3. The insert is rejected with an error
    4. The table automatically accepts duplicates

    Explanation: Inserting duplicates into a UNIQUE column is not allowed; SQLite will reject the operation with an error. Accepting duplicates or deleting them silently would violate the uniqueness rule, and applying a UNIQUE constraint does not turn the column into a primary key.