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.

Practice

1. Which of the following best describes the purpose of integrity constraints in a database?

easy

A. To ensure data accuracy and consistency

B. To speed up database queries

C. To store large amounts of data

D. To create user interfaces

Solution

1. Step 1: Understand the role of integrity constraints

   Integrity constraints are rules that keep data accurate and consistent in a database.

2. Step 2: Match the purpose with the options

   Only To ensure data accuracy and consistency mentions accuracy and consistency, which matches the purpose of integrity constraints.

3. Final Answer:

   To ensure data accuracy and consistency -> Option A

4. Quick Check:

   Integrity constraints = data accuracy and consistency [OK]

Hint: Integrity constraints keep data correct and reliable [OK]

Common Mistakes:

• Confusing constraints with performance features
• Thinking constraints create user interfaces
• Assuming constraints store data

2. Which of the following is the correct syntax to declare a NOT NULL constraint on a column named email in SQL?

easy

A. email VARCHAR(50) NOT NULL

B. email VARCHAR(50) NULL

C. email VARCHAR(50) UNIQUE

D. email VARCHAR(50) PRIMARY KEY

Solution

1. Step 1: Recall the syntax for NOT NULL constraint

   The NOT NULL constraint is added after the data type to ensure the column cannot have empty values.

2. Step 2: Identify the correct option

   email VARCHAR(50) NOT NULL correctly uses email VARCHAR(50) NOT NULL. Other options either allow NULL or use different constraints.

3. Final Answer:

   email VARCHAR(50) NOT NULL -> Option A

4. Quick Check:

   NOT NULL syntax = column datatype NOT NULL [OK]

Hint: NOT NULL means column must have a value, no blanks allowed [OK]

Common Mistakes:

• Using NULL instead of NOT NULL
• Confusing UNIQUE with NOT NULL
• Using PRIMARY KEY when only NOT NULL is needed

3. Consider this SQL table definition:
<pre>CREATE TABLE Students ( ID INT PRIMARY KEY, Name VARCHAR(100) NOT NULL, Age INT CHECK (Age >= 18) );
What will happen if you try to insert (1, 'Alice', 16) into this table?

medium

A. Insertion succeeds without error

B. Insertion fails due to CHECK constraint violation

C. Insertion fails due to PRIMARY KEY violation

D. Insertion fails due to NOT NULL violation

Solution

1. Step 1: Analyze the CHECK constraint on Age

   The CHECK constraint requires Age to be 18 or older.

2. Step 2: Compare the inserted Age value

   The inserted Age is 16, which violates the CHECK constraint.

3. Final Answer:

   Insertion fails due to CHECK constraint violation -> Option B

4. Quick Check:

   Age 16 violates CHECK (Age >= 18) [OK]

Hint: CHECK constraints validate data ranges or conditions [OK]

Common Mistakes:

• Ignoring CHECK constraints and assuming insert works
• Confusing PRIMARY KEY violation with CHECK violation
• Thinking NOT NULL is violated when it is not

4. You have a table with a UNIQUE constraint on the username column. Assume the table already contains a row with username = 'john_doe'. Which of the following SQL statements will cause an error?

medium

A. INSERT INTO Users (username) VALUES ('new_user');

B. INSERT INTO Users (username) VALUES ('jane_smith');

C. INSERT INTO Users (username) VALUES ('john_doe');

D. UPDATE Users SET username = 'jane_smith' WHERE id = 1;

Solution

1. Step 1: Understand UNIQUE constraint behavior

   The UNIQUE constraint prevents duplicate values in the username column.

2. Step 2: Identify duplicate insertion

   INSERT INTO Users (username) VALUES ('john_doe'); tries to insert 'john_doe' which already exists, causing a UNIQUE violation.

3. Final Answer:

   INSERT INTO Users (username) VALUES ('john_doe'); -> Option C

4. Quick Check:

   Duplicate username violates UNIQUE constraint [OK]

Hint: UNIQUE means no duplicates allowed in that column [OK]

Common Mistakes:

• Assuming UPDATE causes error without duplicate
• Thinking inserts of new usernames cause error
• Confusing UNIQUE with PRIMARY KEY

5. A database table has two columns: OrderID and CustomerID. You want to ensure that each order belongs to an existing customer and that no two orders have the same OrderID. Which combination of integrity constraints should you use?

hard

A. NOT NULL on both columns only

B. UNIQUE on CustomerID and NOT NULL on OrderID

C. CHECK constraint on OrderID and UNIQUE on CustomerID

D. PRIMARY KEY on OrderID and FOREIGN KEY on CustomerID referencing Customers table

Solution

1. Step 1: Ensure unique OrderID

   Use PRIMARY KEY on OrderID to guarantee uniqueness and not null.

2. Step 2: Ensure Order belongs to existing Customer

   Use FOREIGN KEY on CustomerID referencing the Customers table to enforce valid customer links.

3. Final Answer:

   PRIMARY KEY on OrderID and FOREIGN KEY on CustomerID referencing Customers table -> Option D

4. Quick Check:

   Unique orders + valid customers = PRIMARY + FOREIGN KEY [OK]

Hint: Use PRIMARY KEY for unique ID, FOREIGN KEY for valid references [OK]

Common Mistakes:

• Using UNIQUE instead of PRIMARY KEY for OrderID
• Missing FOREIGN KEY for CustomerID
• Assuming NOT NULL alone enforces relationships