Constraint naming conventions in SQL - Mini Project: Build & Apply

Constraint Naming Conventions in SQL

📖 Scenario: You are creating a database for a small bookstore. To keep the database organized and easy to maintain, you need to follow proper naming conventions for constraints like primary keys, foreign keys, and unique constraints.

🎯 Goal: Build a SQL table with correctly named constraints following standard naming conventions.

📋 What You'll Learn

Create a table named Books with columns BookID, Title, and AuthorID.

Add a primary key constraint named PK_Books_BookID on BookID.

Add a foreign key constraint named FK_Books_AuthorID referencing Authors(AuthorID).

Add a unique constraint named UQ_Books_Title on Title.

💡 Why This Matters

🌍 Real World

In real databases, naming constraints clearly helps developers and DBAs quickly understand relationships and rules without guessing.

💼 Career

Database developers and administrators must follow naming conventions to keep large projects organized and maintainable.

1

Create the Books table with columns

Write a SQL statement to create a table called Books with columns BookID as integer, Title as varchar(100), and AuthorID as integer.

SQL

-- Create the Books table with columns BookID, Title, and AuthorID
-- Your code here

Hint

Use CREATE TABLE Books and define the columns with their data types.

2

Add a primary key constraint named PK_Books_BookID

Modify the Books table creation to add a primary key constraint named PK_Books_BookID on the BookID column.

SQL

CREATE TABLE Books (
    BookID INT,
    Title VARCHAR(100),
    AuthorID INT,
    -- Add primary key constraint named PK_Books_BookID on BookID
    -- Your code here
);

Hint

Use CONSTRAINT PK_Books_BookID PRIMARY KEY (BookID) inside the table definition.

3

Add a foreign key constraint named FK_Books_AuthorID

Add a foreign key constraint named FK_Books_AuthorID on the AuthorID column referencing the AuthorID column in the Authors table.

SQL

CREATE TABLE Books (
    BookID INT,
    Title VARCHAR(100),
    AuthorID INT,
    CONSTRAINT PK_Books_BookID PRIMARY KEY (BookID),
    -- Add foreign key constraint named FK_Books_AuthorID referencing Authors(AuthorID)
    -- Your code here
);

Hint

Use CONSTRAINT FK_Books_AuthorID FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID) inside the table definition.

4

Add a unique constraint named UQ_Books_Title

Add a unique constraint named UQ_Books_Title on the Title column in the Books table.

SQL

CREATE TABLE Books (
    BookID INT,
    Title VARCHAR(100),
    AuthorID INT,
    CONSTRAINT PK_Books_BookID PRIMARY KEY (BookID),
    CONSTRAINT FK_Books_AuthorID FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID),
    -- Add unique constraint named UQ_Books_Title on Title
    -- Your code here
);

Hint

Use CONSTRAINT UQ_Books_Title UNIQUE (Title) inside the table definition.

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