Page MenuHomePhabricator
Paste P5926

(An Untitled Masterwork)
ActivePublic

Authored by tstarling on Aug 28 2017, 1:20 AM.
Tags
None
Referenced Files
F9189504:
Aug 28 2017, 1:20 AM
Subscribers
None
-- Nullable column doesn't cause automatic primary key
MariaDB [mw]> create table t1 (a int);
Query OK, 0 rows affected (0.02 sec)
MariaDB [mw]> create unique index t1a on t1 (a);
Query OK, 0 rows affected (0.06 sec)
MariaDB [mw]> select * from information_schema.innodb_sys_indexes, information_schema.innodb_sys_tables where innodb_sys_tables.table_id=innodb_sys_indexes.table_id and innodb_sys_tables.name='t1';
+----------+-----------------+----------+------+----------+---------+-------+----------+--------+------+------+--------+-------+
| INDEX_ID | NAME | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | TABLE_ID | SCHEMA | NAME | FLAG | N_COLS | SPACE |
+----------+-----------------+----------+------+----------+---------+-------+----------+--------+------+------+--------+-------+
| 225970 | GEN_CLUST_INDEX | 103685 | 1 | 0 | 2154 | 0 | 103685 | mw | t1 | 1 | 4 | 0 |
| 225971 | t1a | 103685 | 2 | 1 | 2155 | 0 | 103685 | mw | t1 | 1 | 4 | 0 |
+----------+-----------------+----------+------+----------+---------+-------+----------+--------+------+------+--------+-------+
-- Other tests which do create primary keys:
MariaDB [mw]> create table t2 (a int not null);
Query OK, 0 rows affected (0.02 sec)
MariaDB [mw]> create unique index t2a on t2 (a);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [mw]> select * from information_schema.innodb_sys_indexes, information_schema.innodb_sys_tables where innodb_sys_tables.table_id=innodb_sys_indexes.table_id and innodb_sys_tables.name='t2';
+----------+------+----------+------+----------+---------+-------+----------+--------+------+------+--------+-------+
| INDEX_ID | NAME | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | TABLE_ID | SCHEMA | NAME | FLAG | N_COLS | SPACE |
+----------+------+----------+------+----------+---------+-------+----------+--------+------+------+--------+-------+
| 225973 | t2a | 103687 | 3 | 1 | 2157 | 0 | 103687 | mw | t2 | 1 | 4 | 0 |
+----------+------+----------+------+----------+---------+-------+----------+--------+------+------+--------+-------+
1 row in set (0.00 sec)
-- type=3 means primary key
MariaDB [mw]> create table t3 (a int not null, unique key (a));
Query OK, 0 rows affected (0.02 sec)
MariaDB [mw]> select * from information_schema.innodb_sys_indexes, information_schema.innodb_sys_tables where innodb_sys_tables.table_id=innodb_sys_indexes.table_id and innodb_sys_tables.name='t3';
+----------+------+----------+------+----------+---------+-------+----------+--------+------+------+--------+-------+
| INDEX_ID | NAME | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | TABLE_ID | SCHEMA | NAME | FLAG | N_COLS | SPACE |
+----------+------+----------+------+----------+---------+-------+----------+--------+------+------+--------+-------+
| 225974 | a | 103688 | 3 | 1 | 2156 | 0 | 103688 | mw | t3 | 1 | 4 | 0 |
+----------+------+----------+------+----------+---------+-------+----------+--------+------+------+--------+-------+
-- Using PRIMARY KEY in CREATE TABLE generates an index which is the same except for its name
MariaDB [mw]> create table t4 (a int not null primary key);
Query OK, 0 rows affected (0.02 sec)
+----------+---------+----------+------+----------+---------+-------+----------+--------+------+------+--------+-------+
| INDEX_ID | NAME | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | TABLE_ID | SCHEMA | NAME | FLAG | N_COLS | SPACE |
+----------+---------+----------+------+----------+---------+-------+----------+--------+------+------+--------+-------+
| 225975 | PRIMARY | 103689 | 3 | 1 | 2158 | 0 | 103689 | mw | t4 | 1 | 4 | 0 |
+----------+---------+----------+------+----------+---------+-------+----------+--------+------+------+--------+-------+

Event Timeline

MariaDB [mw]> \s
--------------
mysql  Ver 15.1 Distrib 5.5.36-MariaDB, for debian-linux-gnu (x86_64) using readline 5.1

Connection id:		30
Current database:	mw
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		5.5.36-MariaDB-1 (Ubuntu)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	utf8
Client characterset:	latin1
Conn.  characterset:	latin1
UNIX socket:		/var/run/mysqld/mysqld.sock
Uptime:			8 min 51 sec