SQL Composite Key

SQL Composite Key

The SQL Composite Key is a combination of two or more columns in a table that together uniquely identify each record. This command is essential for ensuring data integrity when a single column is not sufficient to uniquely identify records.

---

Syntax

-- To create a composite key when creating a table
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    PRIMARY KEY (column1, column2, ...)
);

-- To add a composite key to an existing table
ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ...);

• CREATE TABLE: This is the SQL keyword used to create a new table.
• ALTER TABLE: This is the SQL keyword used to modify an existing table.
• PRIMARY KEY: This is the SQL keyword used to define a composite primary key.
• column1, column2, ...: These specify the names of the columns that make up the composite key.
• datatype: This specifies the type of data the column can hold, such as INTEGER, VARCHAR, DATE, etc.
• table_name: This specifies the name of the table to create or modify.
• constraint_name: This specifies the name of the composite key constraint.

---

Example

Let's go through a complete example that includes creating a database, creating a table with a composite key, and inserting data into the table.

Step 1: Creating a Database

This step involves creating a new database named example_db.

CREATE DATABASE example_db;

In this example, we create a database named example_db.

Step 2: Creating a Table with a Composite Key

In this step, we create a table named order_items within the previously created database, defining a composite key on the order_id and item_id columns.

USE example_db;

CREATE TABLE order_items (
    order_id INT,
    item_id INT,
    quantity INT,
    price DECIMAL(10, 2),
    PRIMARY KEY (order_id, item_id)
);

Here, we define the order_items table with columns for order_id, item_id, quantity, and price. The combination of order_id and item_id is set as the composite primary key, ensuring each record is uniquely identified by the pair of these columns.

Step 3: Inserting Data into the Table

This step involves inserting some sample data into the order_items table.

INSERT INTO order_items (order_id, item_id, quantity, price) VALUES (1, 101, 2, 19.99);
INSERT INTO order_items (order_id, item_id, quantity, price) VALUES (1, 102, 1, 9.99);
INSERT INTO order_items (order_id, item_id, quantity, price) VALUES (2, 101, 3, 19.99);
INSERT INTO order_items (order_id, item_id, quantity, price) VALUES (2, 103, 5, 29.99);

Here, we insert four rows of data into the order_items table. Each record is uniquely identified by the combination of order_id and item_id.

Step 4: Adding a Composite Key to an Existing Table

This step involves adding a composite key to an existing table named project_assignments that does not have a composite key defined.

ALTER TABLE project_assignments
ADD CONSTRAINT pk_project_assignments PRIMARY KEY (project_id, employee_id);

This command adds a composite primary key to the project_id and employee_id columns of the project_assignments table, ensuring each record is uniquely identified by the pair of these columns.