Referential integrity enforcement in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a foreign key constraint on the Orders table referencing Customers.

SQL

ALTER TABLE Orders ADD CONSTRAINT fk_customer FOREIGN KEY (CustomerID) REFERENCES [1](CustomerID);

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

ACustomers

BEmployees

CProducts

DSuppliers

Attempts:

3 left

💡 Hint

Common Mistakes

Referencing the wrong table like Employees or Products.

Forgetting to specify the referenced column.

The foreign key must reference the primary key of the Customers table to enforce referential integrity.

2fill in blank

medium

Complete the code to create a table with a foreign key that cascades on delete.

SQL

CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  CustomerID INT,
  CONSTRAINT fk_customer FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON DELETE [1]
);

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

ANO ACTION

BSET NULL

CCASCADE

DRESTRICT

Attempts:

3 left

💡 Hint

Common Mistakes

Using SET NULL without allowing NULLs in the foreign key column.

Choosing NO ACTION which does not delete dependent rows.

ON DELETE CASCADE means when a customer is deleted, all their orders are also deleted automatically.

3fill in blank

hard

Fix the error in the foreign key constraint syntax.

SQL

ALTER TABLE Orders ADD CONSTRAINT fk_customer FOREIGN KEY [1] REFERENCES Customers(CustomerID);

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

ACustomerID)

B(CustomerID

CCustomerID(

D(CustomerID)

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting parentheses around the foreign key column.

Misplacing parentheses causing syntax errors.

The foreign key column list must be enclosed in parentheses: (CustomerID).

4fill in blank

hard

Fill both blanks to enforce referential integrity with ON UPDATE CASCADE and ON DELETE SET NULL.

SQL

ALTER TABLE Orders ADD CONSTRAINT fk_customer FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON UPDATE [1] ON DELETE [2];

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

ACASCADE

BSET NULL

CRESTRICT

DNO ACTION

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up ON UPDATE and ON DELETE actions.

Using RESTRICT which prevents deletion or update.

ON UPDATE CASCADE updates foreign keys when the referenced key changes. ON DELETE SET NULL sets the foreign key to NULL when the referenced row is deleted.

5fill in blank

hard

Fill all three blanks to create a table with a foreign key that restricts updates and deletes.

SQL

CREATE TABLE OrderDetails (
  DetailID INT PRIMARY KEY,
  OrderID INT,
  ProductID INT,
  CONSTRAINT fk_order FOREIGN KEY (OrderID) REFERENCES Orders(OrderID) ON UPDATE [1] ON DELETE [2],
  CONSTRAINT fk_product FOREIGN KEY (ProductID) REFERENCES Products(ProductID) ON DELETE [3]
);

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

ARESTRICT

BCASCADE

CNO ACTION

DSET NULL

Attempts:

3 left

💡 Hint

Common Mistakes

Using CASCADE which deletes or updates dependent rows automatically.

Using SET NULL without allowing NULL values.

ON UPDATE RESTRICT and ON DELETE RESTRICT prevent changes if dependent rows exist. ON DELETE NO ACTION also prevents deletion if related rows exist.