|
<< Click to Display Table of Contents >> Navigation: 5. Administration and Customization > 5.7 Database Administration > 5.7.6 Item Table Primary Keys |
Use Item Table Primary Keys to inspect the fields that the program uses as the configured key for item records and, where permitted, add or remove configurable key fields. This is an administrative setting for the current project, rather than an item-number formatting option.
Open Database > Administrator > Item Table Primary Keys…. A project must be open, and you must have system-administrator access.
Understand the key selection
A key identifies a record using the combined values of its selected fields. A field in a composite key does not have to be unique on its own; the complete combination identifies the record.
There are two related parts to the setup:
Part |
Meaning |
Database primary-key fields |
The fields already defined as the primary key of the ITEM table in SQL Server. These fields are locked in the selection window. |
Project key-field configuration |
The key-field list used by the program for the current project. Where the database supports it, configurable fields are stored in TABLE_KEYS for table ITEM and the current ProjectID. |
Saving this window updates the program's key-field configuration. It does not alter the SQL Server primary-key constraint, rebuild the ITEM table or renumber existing items.
Read the field list
Control or column |
Meaning |
Field |
The database column name in the ITEM table, such as ProjectID, WgtGrp, ItemNo or UniqueNo. The available fields depend on the database schema. |
Type |
The SQL data type of the field. It is shown for information and is not edited here. |
Checkbox |
A checked field belongs to the proposed key-field list. Highlighting a row does not by itself include or exclude the field. |
Disabled field |
A field belonging to the database's existing primary key cannot be changed in this window. If UniqueNo is not part of the current key-field list, the program disables the entire list. |
Review the existing checked fields before making a change. Retain the locked fields; this window is not a tool for removing or redesigning the database's physical primary key.
Example: a composite identity
Consider the following illustrative records:
ProjectID |
WgtGrp |
ItemNo |
PROJECT_A |
H1 |
001 |
PROJECT_A |
H2 |
001 |
PROJECT_B |
H1 |
001 |
ItemNo alone does not distinguish these records. The combination of ProjectID, WgtGrp and ItemNo does. This illustrates how a composite identity works; it is not a prescribed checkbox setup. The actual database may also require UniqueNo or other locked fields. If UniqueNo is part of the selected combination, uniqueness of that combination does not establish that ItemNo, or WgtGrp and ItemNo alone, is unique.
Review and save a change
1. Open the correct project and inspect its existing key-field list. When evaluating a different setup, use a project copy first.
2. Check or clear the fields that are available for editing. Choose fields whose values are defined and appropriate for identifying items throughout the project's workflow.
3. Click OK. The program validates the proposed field combination before accepting it.
4. If a validation message appears, review the selection and the relevant item data, then try again or click Cancel.
The program rejects UniqueNo as the only selected field. It also checks that the complete selected-field combination is unique in the ITEM table and that the selected fields contain no SQL NULL values in the current project's records. The uniqueness check queries the ITEM table, so records belonging to other projects in the same database can also affect the result when the selected fields do not distinguish projects.
A SQL NULL means an undefined database value; it is different from an empty text value. Do not replace NULL values with arbitrary values merely to pass the check. Establish which values belong to the records.
After successful validation, the calling command saves the configured fields in TABLE_KEYS when that table is available. Click Cancel to close without accepting the edited selection. Closing or cancelling the selector does not delete or renumber items.
Key fields and item-number format are different
Item Number Format defines how item numbers are constructed or formatted. Item Table Primary Keys defines the program's configured combination of record-identification fields. Changing one is not a substitute for reviewing the other.
See also: 2.9.31 Item Number Format, 7.2 About the SW0x_Project Database and 5.7 Database Administration.
See also: 5.7 Database Administration
See also: 7.2 About the SW0x_Project Database
See also: 2.9.31 Item Number Format