How to Properly Add a Primary Key to a MySQL Table: What\'s the Correct Syntax and What to Avoid?

Adding a Primary Key to a MySQL Table

When you want to add a primary key to an existing table in MySQL, you can use the ALTER TABLE statement. Adding a primary key ensures the uniqueness of each record in the table, a crucial feature for maintaining data integrity.

To add a primary key to the goods table, you can use the following syntax:

ALTER TABLE goods ADD PRIMARY KEY (column_name)

Where column_name is the name of the column you want to set as the primary key.

For example:

<code class="sql">ALTER TABLE goods ADD PRIMARY KEY (id);</code>

However, beware of a common pitfall: specifying only the word PRIMARY instead of PRIMARY KEY. This can lead to errors, as demonstrated by the unsuccessful attempt you mentioned:

<code class="sql">alter table goods add column `id` int(10) unsigned primary AUTO_INCREMENT;</code>

To ensure successful execution, always specify PRIMARY KEY. If you need to add a primary key after adding the column, you can use the following syntax:

<code class="sql">ALTER TABLE goods ADD PRIMARY KEY (id);</code>

By following these guidelines, you can effectively add a primary key to a MySQL table and enhance the integrity of your data.

The above is the detailed content of How to Properly Add a Primary Key to a MySQL Table: What\'s the Correct Syntax and What to Avoid?. For more information, please follow other related articles on the PHP Chinese website!