/
no_pk_innodb_tables.html
169 lines (155 loc) · 7.65 KB
/
no_pk_innodb_tables.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>no_pk_innodb_tables: common_schema documentation</title>
<meta name="description" content="no_pk_innodb_tables: common_schema" />
<meta name="keywords" content="no_pk_innodb_tables: common_schema" />
<link rel="stylesheet" type="text/css" href="css/style.css" />
</head>
<body>
<div id="main">
<div id="header">
<h1>common_schema</h1> <strong>2.2</strong> documentation
<div class="subtitle">DBA's framework for MySQL</div>
</div>
<div id="contentwrapper">
<div id="content">
<h2><a href="no_pk_innodb_tables.html">no_pk_innodb_tables</a></h2>
<h3>NAME</h3>
no_pk_innodb_tables: List InnoDB tables where no PRIMARY KEY is defined
<h3>TYPE</h3>
View
<h3>DESCRIPTION</h3>
<p>InnoDB uses a clustered B+ tree as underlying data structure. Data is clustered via clustering index, which is the PRIMARY KEY in InnoDB.
It follows that any InnoDB table has a PRIMARY KEY, whether one was explicitly defined or not.
</p>
<p>
When no PRIMARY KEY is defined, InnoDB chooses an existing UNIQUE KEY on the table (but does not let us know which).
When no such key is available, it creates an internal PRIMARY KEY, based on <i>row id</i>.
However, it does not provide access to this data.
This leads to a table clustered by some value we cannot access, control, nor define.
It is generally bad practice to create an InnoDB table with no explicit PRIMARY KEY.
</p>
<p><i>no_pk_innodb_tables</i> lists InnoDB tables where PRIMARY KEY is not explicitly created.
It offers a list of candidate keys: UNIQUE keys already defined, which are eligible to take the part of PRIMARY KEY.
</p>
<h3>STRUCTURE</h3>
<blockquote><pre>
mysql> DESC common_schema.no_pk_innodb_tables;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| ENGINE | varchar(64) | YES | | NULL | |
| candidate_keys | longtext | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+
</pre></blockquote>
<h3>SYNOPSIS</h3>
<p>Columns of this view:</p>
<ul>
<li><strong>TABLE_SCHEMA</strong>: schema of InnoDB table missing PRIMARY KEY</li>
<li><strong>TABLE_NAME</strong>: InnoDB table missing PRIMARY KEY</li>
<li><strong>ENGINE</strong>: currently the constant <strong>'InnoDB'</strong></li>
<li><strong>candidate_keys</strong>: Comma seperated list of candidate (UNIQUE) keys, or NULL if no such keys are available.</li>
</ul>
<h3>EXAMPLES</h3>
<p>Show foreign keys create/drop statements for `sakila`.`film_actor` (depends on `film` and `actor` tables)</p>
<blockquote><pre>mysql> ALTER TABLE `sakila`.`rental` MODIFY rental_id INT NOT NULL, DROP PRIMARY KEY, ADD UNIQUE KEY(rental_id);
mysql> CREATE TABLE `test`.`no_pk` (id INT) ENGINE=InnoDB;
mysql> SELECT * FROM common_schema.no_pk_innodb_tables;
+--------------+------------+--------+-----------------------+
| TABLE_SCHEMA | TABLE_NAME | ENGINE | candidate_keys |
+--------------+------------+--------+-----------------------+
| sakila | rental | InnoDB | rental_date,rental_id |
| test | no_pk | InnoDB | NULL |
+--------------+------------+--------+-----------------------+
</pre></blockquote>
<h3>ENVIRONMENT</h3>
MySQL 5.1 or newer
<h3>SEE ALSO</h3>
<a href="candidate_keys.html">candidate_keys</a>,
<a href="redundant_keys.html">redundant_keys</a>
<h3>AUTHOR</h3>
Shlomi Noach
<br/>
</div>
<div id="sidebarwrapper">
<div id="search">
Search online documentation
<form id="search_form" name="search_form" method="GET"
action="http://www.google.com/search"
onsubmit="document.forms['search_form']['q'].value = 'site:http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/ '+document.forms['search_form']['search_term'].value;">
<input type="text" name="search_term" value=""/>
<input type="hidden" name="q" value=""/>
<input type="submit" value="go"/>
</form>
</div>
<div id="menu">
<ul>
<li><a title="Introduction" href="introduction.html">Introduction</a></li>
<li><a title="Documentation" href="documentation.html">Documentation</a></li>
<li><a title="Download" href="download.html">Download</a></li>
<li><a title="Install" href="install.html">Install</a></li>
<li><a title="Risks" href="risks.html">Risks</a></li>
</ul>
<h3>QUERY SCRIPT</h3>
<ul>
<li><a title="QueryScript" href="query_script.html">QueryScript</a></li>
<li><a title="Execution" href="query_script_execution.html">Execution</a></li>
<li><a title="Flow control" href="query_script_flow_control.html">Flow control</a></li>
<li><a title="Statements" href="query_script_statements.html">Statements</a></li>
<li><a title="Expressions" href="query_script_expressions.html">Expressions</a></li>
<li><a title="Variables" href="query_script_variables.html">Variables</a></li>
</ul>
<h3>DEBUG</h3>
<ul>
<li><a title="rdebug" href="rdebug.html">rdebug</a></li>
<li><a title="rdebug API" href="rdebug_api.html">rdebug API</a></li>
<li><a title="rdebug workflow" href="rdebug_workflow.html">Workflow</a></li>
</ul>
<h3>ROUTINES</h3>
<ul>
<li><a title="Execution & flow control" href="execution_routines.html">Execution & flow control</a></li>
<li><a title="General" href="general_routines.html">General</a></li>
<li><a title="Process" href="process_routines.html">Process</a></li>
<li><a title="Query analysis" href="query_analysis_routines.html">Query analysis</a></li>
<li><a title="Schema analysis" href="schema_analysis_routines.html">Schema analysis</a></li>
<li><a title="Security" href="security_routines.html">Security</a></li>
<li><a title="Text" href="text_routines.html">Text</a></li>
<li><a title="Time & date" href="temporal_routines.html">Time & date</a></li>
<li><a title="Charting" href="charting_routines.html">Charting</a></li>
</ul>
<h3>VIEWS</h3>
<ul>
<li><a title="Schema analysis" href="schema_analysis_views.html">Schema analysis</a></li>
<li><a title="Data dimension" href="data_dimension_views.html">Data dimension</a></li>
<li><a title="Process" href="process_views.html">Process</a></li>
<li><a title="Security" href="security_views.html">Security</a></li>
<li><a title="Monitoring" href="monitoring_views.html">Monitoring</a></li>
<li><a title="InnoDB Plugin" href="innodb_plugin_views.html">InnoDB Plugin</a></li>
<li><a title="Percona server" href="percona_server_views.html">Percona Server</a></li>
<li><a title="TokuDB" href="tokudb_views.html">TokuDB</a></li>
</ul>
<h3>DATA</h3>
<ul>
<li><a title="tables" href="tables.html">Tables</a></li>
<li><a title="variables" href="variables.html">Variables</a></li>
</ul>
<h3>META</h3>
<ul>
<li><a title="Help" href="help.html">help</a></li>
<li><a title="Metadata" href="metadata.html">metadata</a></li>
<li><a title="status" href="status.html">status</a></li>
</ul>
</div>
</div>
<div class="clear"> </div>
<div id="footnote" align="center">
<a href="">common_schema</a> documentation
</div>
</div>
</div>
</body>
</html>