SQL Keys - Composite Key

Syntax

To create a table with a composite key, use the following syntax:

CREATE TABLE table_name (
   column1 datatype1,
   column2 datatype2,
   column3 datatype3,
   ...
   CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ...));

Example

Suppose we have a table called "students", which contains information about students in a university. To ensure the uniqueness of the data, we can create a composite key consisting of two columns: "student_id" and "course_id".

CREATE TABLE students (
    student_id INT,
    course_id INT,
    grade INT,
    CONSTRAINT pk_students PRIMARY KEY (student_id, course_id)
);

Output

When we create the "students" table using the above code snippet, we will get a message saying "Query OK, 1 row affected".

Explanation

A composite key is a combination of two or more columns in a table that uniquely identifies each row in the table. In the example above, the "students" table has a composite key that consists of two columns: "student_id" and "course_id". This means that the combination of a particular student ID and course ID must be unique in the table.

Use

Composite keys are useful when you want to ensure the uniqueness of data in a table based on more than one column. They are also useful when you want to create a relationship between two or more tables based on more than one column.

Important Points

• A composite key is a combination of two or more columns in a table that uniquely identifies each row in the table.
• A composite key can consist of any number of columns, but it is typically composed of two or three columns.
• The columns that make up a composite key must be of compatible data types.
• A composite key can be assigned a name, just like a single-column primary key.
• A table can have only one primary key, whether it's a single column or a composite key.

Summary

A composite key is a combination of two or more columns in a table that uniquely identifies each row in the table. This is useful when you want to ensure the uniqueness of data in a table based on more than one column, or when you want to create a relationship between two or more tables based on more than one column. When creating a table with a composite key, be sure to ensure that the columns making up the key are of compatible data types, and that the key is assigned a name. Remember that a table can have only one primary key, whether it's a single column or a composite key.