Constraint naming conventions in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to name a primary key constraint following the convention.

SQL

ALTER TABLE employees ADD CONSTRAINT [1] PRIMARY KEY (employee_id);

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

Aprimary_key

Bpk_employees

Ckey_employee

Demp_pk

Attempts:

3 left

💡 Hint

Common Mistakes

Using generic names like 'primary_key' without table reference.

Not using the 'pk_' prefix.

The convention is to prefix primary key constraints with pk_ followed by the table name.

2fill in blank

medium

Complete the code to name a foreign key constraint following the convention.

SQL

ALTER TABLE orders ADD CONSTRAINT [1] FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

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

Afk_customer

Bforeign_key

Corders_fk

Dfk_orders_customers

Attempts:

3 left

💡 Hint

Common Mistakes

Using vague names like 'foreign_key' without table names.

Omitting the referenced table name.

Foreign key constraints are named with the prefix fk_ followed by the referencing and referenced table names.

3fill in blank

hard

Fix the error in naming a unique constraint following the convention.

SQL

ALTER TABLE products ADD CONSTRAINT [1] UNIQUE (product_code);

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

Auq_products_product_code

Bunique_products_code

Cunique_code

Dproducts_uniq

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'unique_' prefix instead of 'uq_'.

Not including column name in the constraint name.

Unique constraints use the prefix uq_ followed by the table and column names for clarity.

4fill in blank

hard

Fill both blanks to name a check constraint following the convention.

SQL

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

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

Achk_employees_salary

B>

C<

Dsalary_positive

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong prefix like 'check_' instead of 'chk_'.

Using '<' instead of '>' for salary check.

Check constraints use the prefix chk_ followed by table and column names. The condition should ensure salary is greater than zero.

5fill in blank

hard

Fill both blanks to name a default constraint following the convention.

SQL

ALTER TABLE orders ADD CONSTRAINT [1] DEFAULT [2] FOR order_status;

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

Adf_orders_order_status

B'pending'

Ddefault_status

Attempts:

3 left

💡 Hint

Common Mistakes

Using extra syntax after the column name.

Not quoting string default values.

Default constraints use the prefix df_ followed by table and column names. The default value is set without extra syntax after the column name.

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