Database Reference
In-Depth Information
MongoDB Unique Index = RDBMS Unique Index (Candidate Key at Physical Level)
The concept of a candidate key in relational databases is equivalent to the concept of a
unique index in MongoDB. If you want to make sure no two documents can have the same
value in an ISBN, you can create a unique index:
db.title.ensureIndex( { ISBN : 1 }, { “unique” : true } )
The
1
indicates that
ISBN
will be indexed in ascending order (as opposed to
-1
, which
means descending order). The
true
means that
ISBN
must be unique. Note that to create a
non-unique index, which will be discussed shortly, we would set the value to
false
. So if
the above statement is executed and we try to create three documents that have the same
ISBN
, only the first occurrence would be stored and the next two occurrences would be
skipped.
SURROGATE KEY
A surrogate key is a unique identifier for a row in a table, often a counter, that is always
system-generated without intelligence, meaning a surrogate key contains values whose
meanings are unrelated to the entities they identify. (In other words, you can't look at a
month identifier of
1
and assume that it represents the
Month
entity instance value of
Janu-
ary
.) Surrogate keys should not be exposed to the business on user interfaces. They remain
behind the scenes to help maintain uniqueness, to allow for more efficient navigation across
structures, and to facilitate integration across applications.
You've seen that a primary key may be composed of one or more attributes of the entity. A
single-attribute surrogate key is more efficient to use than having to specify three or four
(or five or six) attributes to locate the single record you're looking for. Surrogate keys are
useful for data integration, which is an effort to create a single, consistent version of the
data. Applications such as data warehouses often house data from more than one applica-
tion or system. Surrogate keys enable us to bring together information about the same en-
tity instance that is identified differently in each source system.
When using a surrogate key, always make an effort to determine the natural key, which is
what the business would consider to be the way to uniquely identify the entity, and then
define an alternate key on this natural key. For example, assuming a surrogate key is a more
efficient primary key than
Class Full Name
, we can create the surrogate key
Class ID
for
Class
and define an alternate key on
Class Full Name
. Note that the primary key column
in
Attendance
also changes when we change the primary key of the related entity
Class
.
