Integrity constraints in DBMS Theory - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to add a primary key constraint to the 'id' column.

DBMS Theory

ALTER TABLE employees ADD CONSTRAINT pk_employee_id [1] (id);

Drag options to blanks, or click blank then click option'

APRIMARY KEY

BCHECK

CUNIQUE

DFOREIGN KEY

Attempts:

3 left

💡 Hint

Common Mistakes

Using FOREIGN KEY instead of PRIMARY KEY

Using UNIQUE which allows nulls

Using CHECK which is for conditions

The PRIMARY KEY constraint uniquely identifies each record in a table.

2fill in blank

medium

Complete the code to ensure the 'age' column cannot have NULL values.

DBMS Theory

ALTER TABLE persons MODIFY age [1];

Drag options to blanks, or click blank then click option'

ANULL

BNOT NULL

CDEFAULT 0

DUNIQUE

Attempts:

3 left

💡 Hint

Common Mistakes

Using NULL which allows nulls

Using UNIQUE which only enforces uniqueness

Using DEFAULT which sets a default value but allows NULL

The NOT NULL constraint ensures a column cannot have NULL values.

3fill in blank

hard

Fix the error in the code to add a foreign key constraint referencing 'departments(id)'.

DBMS Theory

ALTER TABLE employees ADD CONSTRAINT fk_dept_id [1] (department_id) REFERENCES departments(id);

Drag options to blanks, or click blank then click option'

AFOREIGN KEY

BPRIMARY KEY

CUNIQUE

DCHECK

Attempts:

3 left

💡 Hint

Common Mistakes

Using PRIMARY KEY instead of FOREIGN KEY

Using UNIQUE which does not create relationships

Using CHECK which is for conditions

The FOREIGN KEY constraint links a column to a primary key in another table.

4fill in blank

hard

Fill both blanks to create a check constraint that ensures salary is greater than 0.

DBMS Theory

ALTER TABLE employees ADD CONSTRAINT chk_salary [1] (salary [2] 0);

Drag options to blanks, or click blank then click option'

ACHECK

B>

C<

DNOT NULL

Attempts:

3 left

💡 Hint

Common Mistakes

Using NOT NULL which only prevents nulls

Using < instead of > causing wrong condition

Omitting CHECK keyword

The CHECK constraint enforces a condition on column values. Here, salary must be greater than 0.

5fill in blank

hard

Fill all three blanks to create a unique constraint on the combination of 'email' and 'phone' columns.

DBMS Theory

ALTER TABLE contacts ADD CONSTRAINT [1] UNIQUE ([2], [3]);

Drag options to blanks, or click blank then click option'

Auq_email_phone

Bemail

Cphone

Dpk_contacts

Attempts:

3 left

💡 Hint

Common Mistakes

Using primary key name instead of unique constraint name

Swapping column names or missing one

Omitting the UNIQUE keyword

A UNIQUE constraint ensures that the combination of 'email' and 'phone' is unique across all rows. Naming the constraint helps identify it.

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