Integrity constraints in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

📋 Factual

intermediate

Understanding Primary Key Constraints

Which of the following statements about primary key constraints in a database is correct?

A A primary key can contain NULL values as long as it uniquely identifies rows.

B A primary key allows duplicate values but no NULL values.

C A primary key uniquely identifies each row and cannot contain NULL values.

D A primary key is optional and used only for indexing purposes.

💡 Hint

Think about what makes each row in a table unique and how NULL values affect uniqueness.

A primary key uniquely identifies each row in a table and cannot contain NULL values. This ensures every record is distinct and identifiable.

🧠 Conceptual

intermediate

Role of Foreign Key Constraints

What is the main purpose of a foreign key constraint in a relational database?

A To prevent any NULL values in a column.

B To ensure that values in one table match values in another table, maintaining referential integrity.

C To enforce that all values in a column are unique within the same table.

D To automatically generate unique values for a column.

💡 Hint

Consider how tables relate to each other and how data consistency is maintained across tables.

A foreign key constraint ensures that the value in one table corresponds to a valid value in another table, preserving the relationship between tables.

🔍 Analysis

advanced

Effect of NOT NULL Constraint on Data Entry

Consider a table with a column defined as NOT NULL. What will happen if you try to insert a row without specifying a value for this column?

A The database will reject the insertion and raise an error.

B The row will be inserted with a default NULL value in that column.

C The database will automatically assign a random value to the column.

D The insertion will succeed but the column will be left empty.

💡 Hint

Think about what NOT NULL means for a column's data.

The NOT NULL constraint requires that a value must be provided for the column. Omitting it causes an error.

❓ Comparison

advanced

Difference Between UNIQUE and PRIMARY KEY Constraints

Which of the following best describes a key difference between UNIQUE and PRIMARY KEY constraints?

A A UNIQUE constraint allows multiple NULL values, but a PRIMARY KEY does not allow any NULL values.

B A PRIMARY KEY allows duplicate values, but a UNIQUE constraint does not.

C Both constraints allow NULL values but differ in indexing methods.

D A UNIQUE constraint is mandatory for every table, but a PRIMARY KEY is optional.

💡 Hint

Consider how NULL values are treated differently by these constraints.

A UNIQUE constraint permits multiple NULLs because NULL is not considered equal to NULL, while a PRIMARY KEY does not allow NULLs at all.

❓ Reasoning

expert

Impact of CHECK Constraints on Data Validity

A table has a CHECK constraint defined as CHECK (age >= 18). What will happen if an attempt is made to insert a row with age = 16?

A The row will be inserted because CHECK constraints are only warnings.

B The row will be inserted but the age value will be ignored.

C The database will automatically change the age to 18 before inserting.

D The insertion will fail with a constraint violation error.

💡 Hint

Think about what a CHECK constraint enforces on data values.

A CHECK constraint enforces a condition that must be true for all rows. Violating it causes the insertion to fail.