I implement a demo patch, based on 5.5.18.
root@localhost : plx 11:54:46> create table test_no_pk (col1 varchar(32));
Query OK, 0 rows affected (0.01 sec)
root@localhost : plx 11:55:05> desc test_no_pk;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| col1 | varchar(32) | YES | | NULL | |
| __row_id | bigint(20) | NO | PRI | NULL | auto_increment |
+----------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
if users has not defined a PK, I will add it automatically.
2. ALTER TABLE
root@localhost : plx 11:55:10> alter table test_no_pk add id int, add primary key(id);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost : plx 11:57:02> desc test_no_pk;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| col1 | varchar(32) | YES | | NULL | |
| id | int(11) | NO | PRI | 0 | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
When users add a PK, I will remove implicit PK automatically.
root@localhost : plx 11:57:07> alter table test_no_pk drop primary key;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost : plx 11:57:42> desc test_no_pk;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| col1 | varchar(32) | YES | | NULL | |
| id | int(11) | NO | | 0 | |
| __row_id | bigint(20) | NO | PRI | NULL | auto_increment |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
When users dropped PK, I will add it automatically.
3. INSERT VALUES
root@localhost : plx 11:59:22> insert into test_no_pk values('abc',2);
ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'
root@localhost : plx 11:59:23> insert into test_no_pk values('abc',4);
ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'
it will report duplicate, Sergei, can you help me to find why?
4. SELECT *
root@localhost : plx 12:07:23> select * from test_no_pk;
+------+----+
| col1 | id |
+------+----+
| abc | 6 |
+------+----+
1 row in set (0.00 sec)
root@localhost : plx 12:07:30> select __row_id from test_no_pk;
+----------+
| __row_id |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
When users run "SELECT *", row_id will be filter.
5. SHOW CREATE
root@localhost : plx 12:07:35> show create table test_no_pk\G
*************************** 1. row ***************************
Table: test_no_pk
Create Table: CREATE TABLE `test_no_pk` (
`col1` varchar(32) DEFAULT NULL,
`id` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
row_id will be hidden.
Thanks,
Lixun