Constraint naming conventions in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Naming Conventions for Primary Key Constraints

Which of the following is the best practice for naming a primary key constraint on a table named Employees?

A PrimaryKey1

B Employees_PK_Constraint

C PK_Employees

D PK1

💡 Hint

Think about clarity and consistency in naming constraints.

Using PK_Employees clearly indicates it is a primary key constraint on the Employees table, following common naming conventions.

❓ query_result

intermediate

Identify the Constraint Name from a Query Result

Given the following SQL query to list constraints on the Orders table, what is the name of the foreign key constraint?

SELECT constraint_name, constraint_type FROM information_schema.table_constraints WHERE table_name = 'Orders';

Assume the output is:

constraint_name       | constraint_type
---------------------|----------------
PK_Orders            | PRIMARY KEY
FK_Orders_Customers  | FOREIGN KEY
CHK_OrderAmount      | CHECK

A FK_Orders_Customers

B CHK_OrderAmount

C PK_Orders

D Orders_FK

💡 Hint

Look for the constraint type FOREIGN KEY.

The foreign key constraint is named FK_Orders_Customers, which follows the convention FK_[Table]_[ReferencedTable].

📝 Syntax

advanced

Identify the Correct Syntax for Naming a Unique Constraint

Which SQL statement correctly creates a unique constraint named UQ_Products_SKU on the SKU column of the Products table?

A ALTER TABLE Products ADD CONSTRAINT UQ_Products_SKU UNIQUE (SKU);

B ALTER TABLE Products ADD UNIQUE CONSTRAINT UQ_Products_SKU (SKU);

C ALTER TABLE Products ADD CONSTRAINT UNIQUE UQ_Products_SKU (SKU);

D ALTER TABLE Products ADD CONSTRAINT UQ_Products_SKU UNIQUE SKU;

💡 Hint

Remember the order: ADD CONSTRAINT <name> UNIQUE (columns).

Option A uses the correct syntax: ADD CONSTRAINT <name> UNIQUE (column). Other options have syntax errors.

❓ optimization

advanced

Choosing Efficient Constraint Names for Large Schemas

In a database with hundreds of tables, which naming convention for foreign key constraints helps optimize readability and maintenance?

A ForeignKeyConstraint

B FK_[RandomNumber]

C FK_[ParentTable]_[ChildTable]

D FK_[ChildTable]_[ParentTable]

💡 Hint

Think about how names help identify relationships quickly.

Using FK_[ChildTable]_[ParentTable] clearly shows which table has the foreign key and which table it references, aiding maintenance.

🔧 Debug

expert

Diagnose the Error in Constraint Naming

Consider this SQL statement:

ALTER TABLE Customers ADD CONSTRAINT PK_Customers PRIMARY KEY (CustomerID);

Later, this statement fails:

ALTER TABLE Customers ADD CONSTRAINT PK_Customers PRIMARY KEY (CustomerID);

What is the most likely cause of the error?

A Primary keys cannot be named with the prefix PK_.

B The constraint name PK_Customers already exists on the Customers table.

C CustomerID column does not exist in Customers table.

D You cannot add a primary key constraint after table creation.

💡 Hint

Think about what happens if you try to add a constraint with a duplicate name.

Constraint names must be unique per table. Trying to add a constraint with an existing name causes an error.