Page MenuHomePhabricator
Files F9189504

No One
Actions

Authored By
tstarling
Aug 28 2017, 1:20 AM
Size
3 KB
Referenced Files
None
Subscribers
None

View Options

-- 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 |
+----------+---------+----------+------+----------+---------+-------+----------+--------+------+------+--------+-------+

File Metadata

Mime Type
text/plain; charset=utf-8
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
4885853
Default Alt Text
(3 KB)

Event Timeline

Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL