- To Generate A Surrogate Key Microsoft Access Uses A Free
- To Generate A Surrogate Key Microsoft Access Uses A Phone
A surrogate key (or synthetic key, entity identifier, system-generated key, database sequence number, factless key, technical key, or arbitrary unique identifier citation needed) in a database is a unique identifier for either an entity in the modeled world or an object in the database. I have to modify an existing table in a Oracle 10g DB with a few thousand records to add a surrogate autonumber key. One way that comes to my mind is to. Create a new sequence Create the id column, allowing null values Updating the id column with the sequence Alter table to add 'not null' and 'primary key' for the new id column. To generate surrogate keys, add a Surrogate Key Generator stage to a job with a single output link to another stage. About this task If you want to pass input columns to the next stage in the job, the Surrogate Key Generator stage can also have an input link.
-->- Now space usage. Table with surrogate key uses 1630 + 256 + 384 = 2270 blocks. Table without surrogate key uses 1566 + 384 = 1950 blocks (although it has allocated the same number of blocks for table probably due to auto segmentspacemanagement). Most insert time gain was 25%, but due to so small numbers miscalculation could be rather high.
- The Microsoft Access 2016 application generator provides the ability to create and store forms, reports, and queries. TRUE Microsoft Access 2016 uses the AutoNumber data type to create surrogate keys.
- To delete the surrogate key source, design a job that uses a Surrogate Key Generator stage by itself, with no input or output links. Updating the state file To update the state file, add a Surrogate Key Generator stage to a job with a single input link from another stage.
Applies To: Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012
If you store surrogate foreign key values in your tables, you will have to correctly work with these values in your Enterprise Portal integration. This involves creating a reference data source to link to the data that is accessed by the surrogate foreign key. It can also include using the AxReferenceField to access the surrogate foreign key value.
Reference Data Sources
A reference data source supplies the data values that are accessed through a surrogate foreign key. A reference data source appears as a child node of the data source that is used for a data set, and is found in the Reference Data Sources node. On your data set, you must create the reference data source before a User Control can use the data that is accessed through the surrogate foreign key.
To create a reference data source
- In the AOT, expand the Data Sets node.
- Expand the node for the data set that you want to add a reference data source for.
- Expand the Data Sources node.
- Expand the data source for the table that contains the surrogate foreign keys that you want to access data for.
- Right-click the Reference Data Sources node, and then click New Reference Data Source.
- Right-click the new reference data source, and then click Properties.
- Set the Name property to specify the name of the reference data source. Use a name that describes the type of data being accessed through the surrogate foreign key.
- Set the JoinRelation property to the relation that indicates which related data to access. The values available correspond to the relations that were defined for the table that is being used for the data source. The relations are the items in the Relations node for the table.
- Save the changes in the AOT.
For example, consider the Facility Management sample application. The FCMWorkOrders table has three foreign key fields defined. Each of these foreign keys reference surrogate keys fields in the parent tables. The first foreign key indicates the room that the work order is associated with. The second indicates the worker that is initiating the work order. The third indicates the worker that is performing the action for the work order. When the FCMWorkOrders table was created, a relation for each one of the surrogate foreign keys had to be added to the table. The following illustration shows these three relations. Notice that two of the relations link to the HcmWorker table.
The FCMWorkOrderDetails data set displays detailed information about a work order. To access the data referenced by the three surrogate foreign keys, a reference data source for each surrogate foreign key is added. The following illustration shows the three reference data sources that were added to access the surrogate foreign key data.
When the FCMWorkOrderDetails data set is accessed in a User Control, the fields that can be accessed through the surrogate foreign keys are available for use. The following illustration shows the Available Fields list in the Bound Field Designer that is accessing fields from the FCMWorkOrderDetails data set. Notice that the list of available fields includes those accessed through the HcmWorkerRequester reference data source that is based on the surrogate foreign key.
To Generate A Surrogate Key Microsoft Access Uses A Free
Surrogate Foreign Key Replacement
If you want to display only the friendly name for a surrogate foreign key, you can use the AxReferenceBoundField component to do this. When you use an AxReferenceBoundField component in a form or grid for a User Control, surrogate foreign key replacement is used to show the value. The actual value shown is the list of fields in the AutoIdentification field group for the row referenced by the surrogate foreign key. The ReferenceField group in the Available Fields list of the Bound Field Designer lists the surrogate foreign key fields for the selected data source. When you select one of the fields from this group, an AxReferenceBoundField component that accesses the surrogate key field is added to the User Control.
The following illustration shows the ReferenceField values (surrogate key values) that are available for the FCMWorkOrders data set. RoomRecId is a surrogate foreign key that is stored in the FCMWorkOrders table. When RoomRecId is selected from the ReferenceField group, an AxReferenceBoundField component is created. The AxReferenceBoundField component uses surrogate foreign key replacement to display the friendly name for the room.
For information about the AxReferenceBoundField component, see Other Bound Field Types.
-->Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012
This topic describes several terms and concepts of keys on data tables, as they apply to Microsoft Dynamics AX.
All keys are unique keys, meaning they disallow duplicate values and null values.
Terminology for Major Concepts of Keys
This section describes the terminology for keys that appear in property names in the AOT Properties window.
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.
Property | Description |
---|---|
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. |
The following AOT image highlights the table properties that are related to keys.
Properties of the AtomicElement demonstration table
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. The AlternateKey property must be set to Yes to make a unique index be an alternate key.
To Generate A Surrogate Key Microsoft Access Uses A Phone
The following table describes properties on the AOT node for an index.
Property | Description |
---|---|
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 ValidFrom and ValidTo fields. The ValidTimeStateKey property cannot be set to Yes when the AlternateKey property is set to No. |
The following image shows that the SymIdx index is an alternate key. Its AlternateKey property is set to Yes.
The properties of the SymIdx index
Relation
In Microsoft Dynamics AX a relation represents a foreign key. The following image shows that the AtomStIdx alternate key of the AtomicState parent table is referenced by this foreign key of the AtomicElement child table. The foreign key is comprised of the AtomicStateName field.
The properties for the AtomStFkyRel relation
The following image displays the AtomStIdx alternate key on the AtomicState table. The previous AtomStFkyRel relation references this alternate key.
The properties of the AtomStIdx alternate key and index
For more information about the properties of table relations, see Table Relation Properties.
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. In the previous image of the AtomicElement table properties, the ReplacementKey property is SymIdx.
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.
Term | Description |
---|---|
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. |
See also
Announcements: New book: 'Inside Microsoft Dynamics AX 2012 R3' now available. Get your copy at the MS Press Store.