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

Need a 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
);

Need a 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
);

Need a 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
);

Need a hint?

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