Saturday, August 10, 2013

Why only DROP PRIMARY KEY affects ALL table rows and no rows are affected on creating PRIMARY KEY?

Why only DROP PRIMARY KEY affects ALL table rows and no rows are affected
on creating PRIMARY KEY?

mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
| 10000 |
+----------+
1 row in set (0.01 sec)
I don't understand the following:
If I drop the primary key it says that all of the rows in my table are
affected:
mysql> alter table employees drop primary key;
Query OK, 10000 rows affected (0.33 sec)
Records: 10000 Duplicates: 0 Warnings: 0
But if I create the primary key 0 rows are affected.
mysql> alter table employees
-> add constraint employees_pk primary key(subsidiary_id, employee_id);
Query OK, 0 rows affected (0.43 sec)
Records: 0 Duplicates: 0 Warnings: 0
I don't understand this. I understand that when I create the primary key a
UNIQUE INDEX is created as a separate datastructure, but why on DROP
PRIMARY KEY all rows are affected and 0 on the creation?

No comments:

Post a Comment