Use SQL statements to add an auto-incrementing primary key or delete a primary key or an SQL primary key to a table.

Use SQL statements to add an auto-incrementing primary key or delete a primary key or an SQL primary key to a table.

When I first met this requirement, I searched the internet and found that I couldn't do it. I had to delete the primary key column and add it again or create a temporary table before importing the data, in fact, it can be directly modified in MYSQL.

Modify the ID field to an auto-increment primary key:

Alter table 'test' change 'id' int (11) unsigned not null AUTO_INCREMENT, add primary key ('id ');

Modify the ID field to auto-increment non-primary key:

Alter table 'test' change 'id' id' int (11) unsigned not null AUTO_INCREMENT, drop primary key;

Modify the ID field to a common field:

Alter table 'test' change 'id' id' int (11) unsigned not null;

Use an SQL statement to copy all rows in the table and insert them to the end of the table (primary key auto-increment). Use an SQL statement to delete duplicate rows.

Example Table: student (id, name, sex. age), id Primary Key auto-Increment
Copy the records in the table and insert them to the end of the table.
Insert into student select name, sex, age from student;
In principle, all columns except the auto-incrementing column must be written. Note that the order of the query fields corresponds to the table, or the following:
Insert into student (sex, name, age) select sex, name, age from student;
The query field corresponds to the inserted Field

Delete duplicate rows
Delete from student where id in (select. id from student a, student B where. name = B. name and. sex = B. sex and. age = B. age and. id <> B. id)
The query statement indicates the id of all duplicate records.
Select. id from student a, student B where. name = B. name and. sex = B. sex and. age = B. age and. id <> B. id
In principle, all fields except the primary creation must be equal.
And a. id <> B. id. If this parameter is not added, the data in the table is duplicated.
Because both a and B are student tables, and they are similar to themselves,
And a. id <> B. id is compared with other records

Running, SQL server2000 Database, no problem
What database do you use?

The primary key of an SQL table is an auto-incrementing sequence. How can I delete the id?

Give you a trigger. When you delete one, the id decreases.

Create trigger delid
On Table Name
For delete
As
Begin
If @ rowcount = 1
Update table name set id = id-1 where id> (select id from deleted)
End

Only one row can be deleted. If multiple rows are deleted, follow up.