JIYIK CN >

Today's tutorial is an introduction to MySQL composite keys. This lesson also discusses the use of the CREATEand ALTERstatements to create composite primary and foreign keys.

Introduction to MySQL composite keys

A composite key is a type of candidate key, which is a combination of multiple columns in a table that allows us to uniquely identify each row of the table. There are two types of composite keys.

1. Composite primary key
2. Composite foreign keys

MySQL composite primary key

A composite primary key is a combination of multiple columns in a table that is used as a primary key, allowing us to uniquely identify each row of the table. Individual columns of a composite primary key in the same table may or may not be foreign keys, they are primary keys in the parent table.

Let's look at the following Entity Relationship Diagram (ERD) in which we create a composite primary key in table using IDcolumns , CUSTOMER_IDand where and are foreign keys at the column level but are primary keys in their parent table.PRODUCT_IDtb_orderCUSTOMER_IDPRODUCT_ID

Some people may be tb_orderconfused by the colors of the keys in Table, as yellow is used for primary keys and red for foreign keys. To make sure we have a composite primary key, click on tb_orderthe word Table PRIMARY.

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 that contains more than one column is called a composite foreign key. All the fields (also called attributes or columns) in the foreign key must point to the exact table.

We cannot create a composite foreign key where one column references table A and the second column references table B. See the following demonstration where two columns ( REFERENCE_NUMBER& REFERENCE_TYPE) of the composite foreign key reference columns ( ACCOUNT_NUMBER& ACCOUNT_TYPE) of accountstable A.

In MySQL, uniqueness of columns is guaranteed only when we combine them. Otherwise, there is no way to maintain uniqueness at the individual level.

A primary key, super key or candidate key can also be called a composite key if they are formed by using more than one attribute (columns) to form the primary key/foreign key.

Whenever a table needs to uniquely identify each row with multiple columns, we can use a composite key for optimal research purposes. However, the columns that we will use to form the composite key can be of various data types.

Therefore, it is not mandatory that the columns used to make a MySQL composite key have the same data type.

Creating Composite Keys in MySQL

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

1. Use CREATEthe statement
2. Use ALTERthe statement

Use CREATEthe statement to create a composite primary key

We will MySQL 复合主键create a composite primary key in the table created using the same database provided in .

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 CREATEthe statement to create a composite foreign key

Next, we use the same database to create MySQL 复合外键the composite foreign key introduced above in .

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 ALTERthe statement to create a composite primary key

Assume that we have created tb_ordera table named .

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:

tb_orderThe table does not have any primary key. Therefore, we can create a composite primary key using statement after the table is created ALTER, because ALTERstatement is used to modify an existing table.

Remember that if we use IDas the primary key, we will not be able to create a composite primary key because a table cannot have two primary keys.

ALTERstatement:

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

Output:

We have successfully created a composite primary key.

Use ALTERthe statement to create a composite foreign key

Assume that we create a sub_accountstable named .

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 do not have a composite foreign key in the output given above. Let us ALTERcreate it using statement as shown 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.