Friday, December 23, 2016
AX 2012 TYPES OF KEYS : Surrogate, Alternate, Replacement, Primary, and Foreign Key
AX 2012 TYPES OF KEYS : Surrogate, Alternate, Replacement, Primary, and Foreign Key
Primary
key:
A
primary key is one type of key. The other type of key is an alternate key.
There is a maximum of one primary key per table, whereas a table can have
several alternate keys. The primary key is usually the type of key that other
tables, called child tables, refer to when a foreign key field in those other
tables need a relational identifier.
Starting
in Microsoft Dynamics AX 2012 the primary key for every new table is always
enforced by an index that has exactly one field. The one field is usually an
incremented number or a completely meaningless number that is generated by the
system. For new tables the default is a primary key based on the RecId field.
This is represented as the surrogate key in the user interface.
The
following table describes the PrimaryIndex property and other major
properties that are related to keys.
PrimaryIndex
The
drop-down list contains the surrogate key plus every index on the table that
has its AlternateKey property set to Yes.
CreateRecIdIndex
This
property controls whether the system creates a unique index on the RecId field.
The default value is Yes. This is the basis of the surrogate key.
No
other field is added to this index, not even DataAreaId.
ReplacementKey
The
drop-down list contains every index that has its AlternateKey property
set to Yes.
You
might change the default blank value to an index whose field values within each
record provide a name or other moniker that is meaningful to people. If a ReplacementKey is
chosen, its fields can appear on forms to helpfully identify each record.
The ReplacementKey should
be a set of fields that represent the natural key.
ClusterIndex
The ClusterIndex value
is given to the underlying Microsoft SQL Server database system as a
performance tuning choice. This choice generally controls the physical sequence
in which the records are stored in the underlying database.
Alternate
Key
A
table can have several alternate keys. Any one alternate key can switch to
being the primary key, if the alternate key is comprised of only one field.
A
table can reference the alternate key of another table. However, it is more common
for a table to reference the primary key of another table. As an option, an
alternate key can be chosen as the ReplacementKey of a table.
In
practice each alternate key relies on a unique index for its implementation and
enforcement. However, a unique index alone does not make an alternate key. TheAlternateKey property
must be set to Yes to make a unique index be an alternate key.
The
following table describes properties on the AOT node for an index.
AllowDuplicates
No means
that the combined fields of the index must together make a value in each record
which no other record has.
AlternateKey
Yes means
that other tables can create foreign key relations that reference this key, as
an alternative to referencing the primary key.
Indexes
with two or more fields cannot have their AlternateKey property value
set to Yes.
ValidTimeStateKey
A
key that is marked as a valid time state key is not a candidate key for child
tables to reference in their foreign key relations. Instead, this key is meant
for managing date effective data in its own table.
The
default is No. This field can be Yes only if the ValidTimeStateFieldType property
is Yes on the table. Yes means this key contains the ValidFromand ValidTo fields.
The ValidTimeStateKey property
cannot be set to Yes when the AlternateKey property is set
to No.
ReplacementKey
A
replacement key is an alternate key that the system can display on forms
instead of a meaningless numeric primary key value. Each table can have a
maximum of one replacement key.
The
replacement key is chosen by setting the ReplacementKey property on
the table. The drop-down list offers every alternate key as an available value.
Other Terminology for Keys
In
Microsoft Dynamics AX, there are other terms that are used to describe table
keys. These terms do not appear as property names in Microsoft Dynamics AX.
These terms are described in the following table.
Foreign
key
In
Microsoft Dynamics AX, an AOT node under MyTable > Relations represents
a foreign key. For more information, see the previous Relations section
in this topic.
Natural
key
A
key whose value has meaning to people. Most replacement keys are natural keys.
Surrogate
key
A
key whose value has no meaning to people. A large number generated by the
system, such as RecId, could be a surrogate key.
Unique
key
A
broad term that applies to primary keys and to alternate keys. It does not
apply to foreign keys. This term emphasizes that all values for a given key
must be unique within one table. All fields in a unique key must be
not-nullable.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment