如何在MySQL中的現有表中添加主鍵？

要為現有表添加主鍵，需使用ALTER TABLE語句配合ADD PRIMARY KEY子句。 1. 確保目標列無NULL值、無重複且定義為NOT NULL；2. 單列主鍵語法為ALTER TABLE 表名ADD PRIMARY KEY (列名)；3. 多列組合主鍵語法為ALTER TABLE 表名ADD PRIMARY KEY (列1, 列2)；4. 若列允許NULL，需先執行MODIFY設置為NOT NULL；5. 每張表僅能有一個主鍵，添加前需刪除舊主鍵；6. 如需自增，可使用MODIFY設置AUTO_INCREMENT。操作前確保數據完整性，步驟完成後主鍵將成功添加。

To add a primary key to an existing table in MySQL, you use the ALTER TABLE statement with the ADD PRIMARY KEY clause. Here's how to do it properly.

Make Sure the Column Meets Primary Key Requirements

Before adding a primary key, ensure the column:

• Does not contain NULL values.
• Contains unique values (no duplicates).
• Is defined as NOT NULL .

If the column allows NULL or has duplicates, the operation will fail.

Add a Primary Key to a Single Column

If you want to set an existing column (eg, id ) as the primary key:

 ALTER TABLE your_table_name ADD PRIMARY KEY (id);

For example:

 ALTER TABLE users ADD PRIMARY KEY (id);

This assumes the id column already exists, is NOT NULL , and has unique values.

Add a Composite Primary Key (Multiple Columns)

If the primary key should consist of multiple columns:

 ALTER TABLE your_table_name ADD PRIMARY KEY (column1, column2);

Example:

 ALTER TABLE order_items ADD PRIMARY KEY (order_id, product_id);

This creates a primary key across both columns, ensuring the combination is unique.

Common Issues and Tips

• Column must be indexed and NOT NULL : If the column isn't already NOT NULL , you may need to modify it first:

   ALTER TABLE your_table_name MODIFY id INT NOT NULL;
  ALTER TABLE your_table_name ADD PRIMARY KEY (id);

• Only one primary key per table : You can't add a second primary key unless you drop the existing one:

   ALTER TABLE your_table_name DROP PRIMARY KEY, ADD PRIMARY KEY (new_column);

• Auto-increment consideration : If using an INT as a primary key and want it to auto-increment:

   ALTER TABLE your_table_name MODIFY id INT AUTO_INCREMENT PRIMARY KEY;

  Or in one step:

   ALTER TABLE your_table_name ADD PRIMARY KEY (id);
  ALTER TABLE your_table_name MODIFY id INT AUTO_INCREMENT;

  ---

  Basically, just use ALTER TABLE ... ADD PRIMARY KEY , ensure data integrity first, and handle NOT NULL and uniqueness. It's straightforward once the column is clean.

  以上是如何在MySQL中的現有表中添加主鍵？的詳細內容。更多資訊請關注PHP中文網其他相關文章！