Composite Key Using CREATE Statement
Here, we will look at how composite keys operate in MySQL. Let us first construct a table called “employees” using the following statement:
CREATE TABLE employee (
employee_id INT,
department_id INT,
employee_name VARCHAR(255),
PRIMARY KEY (employee_id, department_id)
);
In this example, the employee table is created with three columns: employee_id, department_id, and employee_name. The PRIMARY KEY constraint is applied to the combination of employee_id and department_id. This means that each pair of employee_id and department_id values must be unique within the table.
We can verify the same using the command as below:
DESCRIBE employee;
After the successful execution, It means we have successfully added the composite primary key on employee_id and department_id columns.
Next, we need to insert the values into this table as given below:
INSERT INTO employee (employee_id, department_id, employee_name) VALUES
(101, 1, 'John Doe'),
(102, 2, 'Jane Smith'),
(103, 1, 'Bob Johnson'),
(104, 3, 'Alice Brown');
Next, execute the below command to show the table data:
SELECT * FROM employee;
Output:
MySQL COMPOSITE KEY
In MySQL, a composite key is a combination of two or more columns in a table that uniquely identifies each entry. It is a candidate key made up of many columns. MySQL guarantees column uniqueness only when they are concatenated. If they are extracted separately, the uniqueness cannot be maintained.
Any key, such as the primary key, super key, or candidate key, can be referred to as a composite key if it has more than one characteristic. A composite key is important when the database has to uniquely identify each record that contains more than one characteristic. A composite key column may include a variety of data types. Thus, the columns do not have to have the same data type to form a composite key in MySQL.
A composite key can be added in two ways:
- Using CREATE Statement
- Using ALTER Statement
Let us see both ways in detail.