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.

Practice

1. What is the main reason to use clear and consistent names for SQL constraints?

easy

A. To make databases easier to understand and maintain

B. To speed up query execution

C. To reduce storage space

D. To avoid using indexes

Solution

1. Step 1: Understand the purpose of constraint names

   Constraint names help identify rules applied to database tables.

2. Step 2: Recognize the benefit of clear naming

   Clear and consistent names make it easier for developers and DBAs to understand and maintain the database structure.

3. Final Answer:

   To make databases easier to understand and maintain -> Option A

4. Quick Check:

   Clear naming = easier maintenance [OK]

Hint: Clear names help everyone understand constraints fast [OK]

Common Mistakes:

• Thinking constraint names affect query speed
• Confusing constraint names with indexes
• Ignoring naming conventions

2. Which of the following is the correct way to name a primary key constraint on a table named Employees?

easy

A. PrimaryKey_Employees

B. PK_Employees

C. Employees_PK

D. KeyPrimary_Employees

Solution

1. Step 1: Identify the common prefix for primary key constraints

   Primary key constraints commonly start with PK_.

2. Step 2: Combine prefix with table name

   The convention is prefix + underscore + table name, so PK_Employees is correct.

3. Final Answer:

   PK_Employees -> Option B

4. Quick Check:

   Primary key prefix = PK_ [OK]

Hint: Use PK_ prefix plus table name for primary keys [OK]

Common Mistakes:

• Using full words like PrimaryKey_ instead of PK_
• Placing prefix after table name
• Mixing words in wrong order

3. Given the following constraint name on a table Orders: FK_Orders_Customers, what type of constraint is this?

medium

A. Foreign key constraint

B. Check constraint

C. Unique constraint

D. Primary key constraint

Solution

1. Step 1: Analyze the prefix in the constraint name

   The prefix FK_ stands for Foreign Key.

2. Step 2: Confirm the constraint type

   Since the name is FK_Orders_Customers, it indicates a foreign key from Orders to Customers table.

3. Final Answer:

   Foreign key constraint -> Option A

4. Quick Check:

   FK_ prefix = Foreign Key [OK]

Hint: FK_ prefix means foreign key constraint [OK]

Common Mistakes:

• Confusing FK_ with primary key PK_
• Assuming FK_ means unique constraint
• Ignoring prefix meaning

4. You wrote this constraint name for a unique constraint on the Products table: UQProducts. What is the issue with this name?

medium

A. It should include the column name

B. It uses the wrong prefix for unique constraints

C. It is missing an underscore after the prefix

D. It is too long

Solution

1. Step 1: Identify the correct prefix and format for unique constraints

   Unique constraints use the prefix UQ_ with an underscore.

2. Step 2: Check the given name format

   The name UQProducts misses the underscore after UQ, so it should be UQ_Products.

3. Final Answer:

   It is missing an underscore after the prefix -> Option C

4. Quick Check:

   Unique prefix = UQ_ with underscore [OK]

Hint: Always put underscore after prefix like UQ_ [OK]

Common Mistakes:

• Skipping underscore after prefix
• Using wrong prefix like UK_
• Adding column name unnecessarily

5. You want to name a check constraint on the Employees table that ensures salary is positive. Which of these names follows best practice for constraint naming conventions?

hard

A. SalaryPositiveCheck

B. CheckSalaryPositive

C. EmployeesCKSalary

D. CK_Employees_SalaryPositive

Solution

1. Step 1: Identify the prefix for check constraints

   Check constraints use the prefix CK_.

2. Step 2: Combine prefix with table name and descriptive suffix

   Best practice is prefix + table name + description, so CK_Employees_SalaryPositive is clear and consistent.

3. Final Answer:

   CK_Employees_SalaryPositive -> Option D

4. Quick Check:

   Check prefix = CK_ plus table name [OK]

Hint: Use CK_ + table name + description for check constraints [OK]

Common Mistakes:

• Using no prefix or wrong prefix
• Not including table name
• Using unclear or inconsistent names