How to set composite primary key in mysql

The methods to set a composite primary key in MySQL are: specify or use the ALTER TABLE statement when creating a table, which is used to uniquely identify each row and improve query performance and data integrity. Advantages include finding rows quickly, enforcing uniqueness, and maintaining uniqueness across multiple columns, but the disadvantages are increased storage overhead and performance impact.

Set a composite primary key in MySQL

The composite primary key is a unique index containing multiple columns, which is used to Uniquely identifies each row in the table. It is often used to create more unique indexes to improve query performance and data integrity.

How to set the composite primary key:

1. Specify when creating the table:

<code class="sql">CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  PRIMARY KEY (column1, column2)
);</code>

2. Use ALTER TABLE statement:

<code class="sql">ALTER TABLE table_name ADD PRIMARY KEY (column1, column2);</code>

Example:

Create a table named customers, where The first_name and last_name columns constitute the composite primary key:

<code class="sql">CREATE TABLE customers (
  customer_id INT NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL,
  PRIMARY KEY (first_name, last_name)
);</code>

Advantages:

• Improve query performance because the primary key Used to quickly find rows.
• Ensures data integrity as the same pair of values ​​cannot be used in multiple rows.
• Allows uniqueness to be enforced across multiple columns.

Limitations:

• Increases storage overhead due to the need to maintain indexes on multiple columns.
• Deleting or updating any part of a primary key column may impact performance because the index will need to be rebuilt.

The above is the detailed content of How to set composite primary key in mysql. For more information, please follow other related articles on the PHP Chinese website!