Introduction to the MySQL Composite Key

1. Introduction to the MySQL Composite Key
2. Create a Composite Key in MySQL

Today’s tutorial is an introduction to the MySQL composite key. This lesson also discusses creating a composite primary and foreign key using the CREATE & ALTER statements.

Introduction to the MySQL Composite Key

A composite key is one of the types of candidate key, which is a combination of multiple columns within the table and lets us identify the table’s every row uniquely. There are two types of composite keys.

1. Composite Primary Key
2. Composite Foreign Key

MySQL Composite Primary Key

The composite primary key is a combination of multiple columns within the table that are used as the primary key and let us identify the table’s every row uniquely. The individual columns of a composite primary key can or can’t be foreign keys in the same table which are the primary keys in the parent table.

Let’s see the following entity-relationship diagram (ERD) where we make a composite primary key in table tb_order by using the columns ID, CUSTOMER_ID, and PRODUCT_ID where CUSTOMER_ID & PRODUCT_ID are foreign keys on the column level but primary keys in their parent table.

Some of you might be confused with the keys’ color in the tb_order table because yellow is used for the primary key, and red represents the foreign keys. To ensure we have a composite primary key, click on the word PRIMARY in the tb_order table.

It will highlight all the columns involved in making a composite primary key (see the following screenshot as a demonstration).

MySQL Composite Foreign Key

A foreign key containing multiple columns is called a composite foreign key. All the fields (also called attributes or columns) in a foreign key must point to the exact table.

We can’t create a composite foreign key where one column references Table A and the second column references Table B. See the following demonstration where both columns (REFERENCE_NUMBER & REFERENCE_TYPE) of composite foreign key reference the columns (ACCOUNT_NUMBER & ACCOUNT_TYPE) of the accounts table.

In MySQL, the column’s uniqueness is only guaranteed when we combine them. Otherwise, the uniqueness can not be maintained on an individual level.

The primary, super, or candidate key can also be known as the composite key if they are formed by using multiple attributes (columns) to make a primary/foreign key.

We can use the composite key whenever the table requires identifying every row with multiple columns uniquely for best research purposes. However, the column we will use to form the composite key can have various data types.

Therefore, having the same data type for the columns used to make a MySQL composite key is not mandatory.

Create a Composite Key in MySQL

In MySQL, we can create a composite primary key and composite foreign key in the following two ways.

1. Use the CREATE statement
2. Use the ALTER statement

Use the CREATE Statement for Creating a Composite Primary Key

We will create a composite primary key at the table created using the same database presented in the MySQL Composite Primary Key.

Code:

CREATE TABLE `db_ms20`.`tb_order` (
    ID INT NOT NULL,
    CUSTOMER_ID INT NOT NULL,
    PRODUCT_ID INT NOT NULL,
    CONSTRAINT FK_CUSTOMER_ID FOREIGN KEY (CUSTOMER_ID)
    REFERENCES tb_customer(ID),
    CONSTRAINT FK_PRODUCT_ID FOREIGN KEY (PRODUCT_ID)
    REFERENCES tb_product(ID),

    PRIMARY KEY (ID, CUSTOMER_ID, PRODUCT_ID)
);

Use the CREATE Statement for Creating a Composite Foreign Key

Next, we use the same database to create a composite foreign key presented above in the MySQL Composite Foreign Key.

Code:

CREATE TABLE `ms20`.`sub_accounts` (
   SUB_ACCOUNT INTEGER PRIMARY KEY,
   REFERENCE_NUMBER INTEGER NOT NULL,
   REFERENCE_TYPE INTEGER NOT NULL,
   SUB_ACC_DESCRIPTION CHAR(20),

   FOREIGN KEY (REFERENCE_NUMBER, REFERENCE_TYPE)
   REFERENCES accounts
   (ACCOUNT_NUMBER, ACCOUNT_TYPE)
);

Use the ALTER Statement for Creating a Composite Primary Key

Just assume that we have created the table named tb_order.

Code:

CREATE TABLE `db_ms20`.`tb_order` (
    ID INT NOT NULL,
    CUSTOMER_ID INT NOT NULL,
    PRODUCT_ID INT NOT NULL,
    CONSTRAINT FK_CUSTOMER_ID FOREIGN KEY (CUSTOMER_ID)
    REFERENCES tb_customer(ID),
    CONSTRAINT FK_PRODUCT_ID FOREIGN KEY (PRODUCT_ID)
    REFERENCES tb_product(ID)
);

Output:

We don’t have any primary key in the tb_order table. So, we can use the ALTER statement for creating a composite primary key after the table’s creation because the ALTER statement is used to modify the existing table.

Remember, if we had the ID as the primary key, we would not be able to create a composite primary key because one table can not have two primary keys.

The ALTER Statement:

ALTER TABLE `db_ms20`.`tb_order` ADD PRIMARY KEY(ID, CUSTOMER_ID, PRODUCT_ID);

Output:

We have successfully created a composite primary key.

Use the ALTER Statement for Creating a Composite Foreign Key

Suppose that we have created a table named sub_accounts.

Code:

CREATE TABLE `ms20`.`sub_accounts` (
   SUB_ACCOUNT INTEGER PRIMARY KEY,
   REFERENCE_NUMBER INTEGER NOT NULL,
   REFERENCE_TYPE INTEGER NOT NULL,
   SUB_ACC_DESCRIPTION CHAR(20)
);

Output:

We don’t have a composite foreign key in the output given above. Let’s create it using the ALTER statement, which is given below:

ALTER TABLE `ms20`.`sub_accounts` ADD FOREIGN KEY
(REFERENCE_NUMBER, REFERENCE_TYPE)
REFERENCES accounts
(ACCOUNT_NUMBER, ACCOUNT_TYPE);

Output:

Compare this output with the previous one and observe that we successfully created the composite foreign key.