Movatterモバイル変換


[0]ホーム

URL:


Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

Sign up
Appearance settings

Commit61ff1c3

Browse files
author
Thomas G. Lockhart
committed
Nice exposition on indices and keys from Herouth Maoz which appeared
on the mailing lists a while ago. Maybe slightly changed to fit docs.Will go into the User's Guide.
1 parent45816ec commit61ff1c3

File tree

1 file changed

+168
-0
lines changed

1 file changed

+168
-0
lines changed

‎doc/src/sgml/keys.sgml

Lines changed: 168 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,168 @@
1+
<!--
2+
$Header: /cvsroot/pgsql/doc/src/sgml/Attic/keys.sgml,v 1.1 1998/08/15 06:52:03 thomas Exp $
3+
Indices and Keys
4+
5+
$Log: keys.sgml,v $
6+
Revision 1.1 1998/08/15 06:52:03 thomas
7+
Nice exposition on indices and keys from Herouth Maoz which appeared
8+
on the mailing lists a while ago. Maybe slightly changed to fit docs.
9+
Will go into the User's Guide.
10+
11+
-->
12+
13+
<chapter id="keys">
14+
<docinfo>
15+
<authorgroup>
16+
<author>
17+
<firstname>Herouth</firstname>
18+
<surname>Maoz</surname>
19+
</author>
20+
</authorgroup>
21+
<date>1998-03-02</date>
22+
</docinfo>
23+
24+
<Title>Indices and Keys</Title>
25+
26+
<Note>
27+
<Title>Author</Title>
28+
<Para>
29+
Written by
30+
<ULink url="herouth@oumail.openu.ac.il">Herouth Maoz</ULink>
31+
</Para>
32+
</Note>
33+
34+
<Note>
35+
<Title>Editor's Note</Title>
36+
<Para>
37+
This originally appeared on the mailing list
38+
in response to the question:
39+
"What is the difference between PRIMARY KEY and UNIQUE constraints?".
40+
</Para>
41+
</Note>
42+
43+
<ProgramListing>
44+
Subject: Re: [QUESTIONS] PRIMARY KEY | UNIQUE
45+
46+
What's the difference between:
47+
48+
PRIMARY KEY(fields,...) and
49+
UNIQUE (fields,...)
50+
51+
- Is this an alias ?
52+
- If PRIMARY KEY is already unique, then why
53+
there's another kind of key named UNIQUE ?
54+
</ProgramListing>
55+
56+
<Para>
57+
A primary key is the field(s) used to identify a specific row. For example,
58+
Social Security numbers identifying a person.
59+
</Para>
60+
<Para>
61+
A simply UNIQUE combination of fields has nothing to do with identifying
62+
the row. It's simply an integrity constraint. For example, I have
63+
collections of links. Each collection is identified by a unique number,
64+
which is the primary key. This key is used in relations.
65+
</Para>
66+
<Para>
67+
However, my application requires that each collection will also have a
68+
unique name. Why? So that a human being who wants to modify a collection
69+
will be able to identify it. It's much harder to know, if you have two
70+
collections named "Life Science", the the one tagged 24433 is the one you
71+
need, and the one tagged 29882 is not.
72+
</Para>
73+
<Para>
74+
So, the user selects the collection by its name. We therefore make sure,
75+
withing the database, that names are unique. However, no other table in the
76+
database relates to the collections table by the collection Name. That
77+
would be very inefficient.
78+
</Para>
79+
<Para>
80+
Moreover, despite being unique, the collection name does not actually
81+
define the collection! For example, if somebody decided to change the name
82+
of the collection from "Life Science" to "Biology", it will still be the
83+
same collection, only with a different name. As long as the name is unique,
84+
that's OK.
85+
</Para>
86+
<Para>
87+
So:
88+
89+
<itemizedlist>
90+
<ListItem>
91+
<Para>
92+
Primary key:
93+
<itemizedList Mark="bullet" Spacing="compact">
94+
<ListItem>
95+
<Para>
96+
Is used for identifying the row and relating to it.
97+
</Para>
98+
</ListItem>
99+
<ListItem>
100+
<Para>
101+
Is impossible (or hard) to update.
102+
</Para>
103+
</ListItem>
104+
<ListItem>
105+
<Para>
106+
Should not allow NULLs.
107+
</Para>
108+
</ListItem>
109+
</itemizedlist>
110+
111+
<ListItem>
112+
<Para>
113+
Unique field(s):
114+
<itemizedlist Mark="bullet" Spacing="compact">
115+
<ListItem>
116+
<Para>
117+
Are used as an alternative access to the row.
118+
</Para>
119+
</ListItem>
120+
<ListItem>
121+
<Para>
122+
Are updateable, so long as they are kept unique.
123+
</Para>
124+
</ListItem>
125+
<ListItem>
126+
<Para>
127+
NULLs are acceptable.
128+
</Para>
129+
</ListItem>
130+
</itemizedlist>
131+
</itemizedlist>
132+
133+
<Para>
134+
As for why no non-unique keys are specifiable by SQL syntax? Well - you
135+
must understand that indexes are implementation-dependent. SQL does not
136+
define the implementation, merely the relations between data in the
137+
database.
138+
</Para>
139+
<Para>
140+
Thus, you may query a table by any combination of its columns, despite the
141+
fact that you don't have an index on these columns. The indexes are merely
142+
an implementational aid which each RDBMS offers you, in order to cause
143+
commonly used queries to be done more efficiently. Some RDBMS may give you
144+
additional measures, such as keeping a key stored in main memory. They will
145+
have a special command, for example
146+
<programlisting>
147+
CREATE MEMSTORE ON &lt;table&gt; COLUMNS &lt;cols&gt;
148+
</programlisting>
149+
(this is not an existing command, just an example).
150+
</Para>
151+
<Para>
152+
In fact, when you create a primary key or a unique combination of fields,
153+
nowhere in the SQL specification does it say that an index is created, nor that
154+
the retrieval of data by the key is going to be more efficient than a
155+
sequential scan!
156+
</Para>
157+
<Para>
158+
So, if you want to use a combination of fields which is not unique as a
159+
secondary key, you really don't have to specify anything - just start
160+
retrieving by that combination! However, if you want to make the retrieval
161+
efficient, you'll have to resort to the means your RDBMS provider gives you
162+
- be it an index, my imaginary MEMSTORE command, or an intelligent RDBMS
163+
which crates indices without your knowledge based on the fact that you have
164+
sent it many queries based on a specific combination of keys... (It learns
165+
from experience).
166+
</Para>
167+
</chapter>
168+

0 commit comments

Comments
 (0)

[8]ページ先頭

©2009-2025 Movatter.jp