SQL Composite Keys 🎯

Welcome back to CodeYourCraft! Today, we're diving into the world of SQL and learning about Composite Keys. If you're new to SQL, no worries! We'll start from the ground up and explain everything you need to know.

What is a Composite Key? 📝

In a database, a Composite Key (also known as a Compound Key) is a primary key that is formed by combining multiple columns. This unique combination ensures that each row in a table can be identified uniquely.

Let's understand this with an example. Imagine a database for a library. Each book has a unique combination of ISBN and Edition. So, in this case, ISBN and Edition together form a Composite Key for the Books table.

Why Use Composite Keys? 💡

Composite Keys are useful when a single column cannot guarantee a unique value for each row. By combining multiple columns, we can create a unique identifier for each record.

Creating a Composite Key in SQL ✅

Now, let's see how to create a Composite Key in SQL. We'll use the following schema for our library database.

sql

CREATE TABLE Books (
    ISBN VARCHAR(13),
    Title VARCHAR(255),
    Edition INT,
    PRIMARY KEY (ISBN, Edition)
);

Here, we've created a Books table with ISBN, Title, and Edition columns. The PRIMARY KEY (ISBN, Edition) line indicates that both ISBN and Edition together form the Composite Key.

Practical Example 🎯

Let's add some books to our table.

sql

INSERT INTO Books (ISBN, Title, Edition)
VALUES ('123-4567-8901-2', 'The Catcher in the Rye', 1),
       ('123-4567-8901-3', 'The Catcher in the Rye', 2),
       ('234-5678-9012-1', 'To Kill a Mockingbird', 1);

In the example above, we've added two editions of 'The Catcher in the Rye' and 'To Kill a Mockingbird'. Each row is uniquely identified by its Composite Key (ISBN and Edition).

Quiz 💡

Question: Which of the following is a correct example of a Composite Key?

A: ISBN B: ISBN and Edition C: Title

Correct: B Explanation: B is correct because ISBN and Edition together uniquely identify each book.

That's it for today! We hope you found this lesson on SQL Composite Keys useful. Stay tuned for more SQL tutorials on CodeYourCraft. Happy coding! 🚀