Dynamics 365 Business Central: How to quickly check if a field is a primary key field

Hi, Readers.
Today I would like to share another mini tip about Business Central, how to quickly check if a field is a primary key field.

In AL, a key definition is a sequence of one or more field IDs from a table. You can define keys in table objects and table extension objects, depending on the type of key. There are two types of keys: primary and secondary.

• Primary keys: A primary key uniquely identifies each record in a table. Every table has a primary key, and there can only be one primary key per table. Primary keys are defined on table objects only. In SQL, table extension objects inherit the primary key of the table object they extend (the base table object). So any key that you define in a table extension object is considered a secondary key.
• Secondary keys: Secondary keys create indexes in SQL. They’re defined in both table objects and table extension objects. You can define multiple secondary keys for a single table object and table extension object.

More details: Table keys

Let’s look at two examples.

table 36 “Sales Header”:

table 37 “Sales Line”:

In Dynamics 365 Business Central: How to export all keys in all tables, we previously discussed how to use table 2000000063 “Key” to view or export all keys. We can check if a field is a primary key field using this table.

But is there a standard method to do this? Yes, We can find a reference in the codeunit below.
codeunit 9800 User:

Standard code: You only need to pass in the table ID and field ID, and it can even determine which number the field is within the primary key sequence.

    local procedure IsPrimaryKeyField(TableID: Integer; FieldID: Integer; var NumberOfPrimaryKeyFields: Integer): Boolean
    var
        RecRef: RecordRef;
        KeyRef: KeyRef;
    begin
        RecRef.Open(TableID);
        KeyRef := RecRef.KeyIndex(1);
        NumberOfPrimaryKeyFields := KeyRef.FieldCount;
        exit(IsKeyField(TableID, FieldID));
    end;

    local procedure IsKeyField(TableID: Integer; FieldID: Integer): Boolean
    var
        RecRef: RecordRef;
        FieldRef: FieldRef;
        KeyRef: KeyRef;
        KeyFieldCount: Integer;
    begin
        RecRef.Open(TableID);
        KeyRef := RecRef.KeyIndex(1);
        for KeyFieldCount := 1 to KeyRef.FieldCount do begin
            FieldRef := KeyRef.FieldIndex(KeyFieldCount);
            if FieldRef.Number = FieldID then
                exit(true);
        end;

        exit(false);
    end;

Therefore, we can directly copy and use these methods.

Great.

Very simple, give it a try!!!😁

Test code:

pageextension 50127 CustomerListExt extends "Customer List"
{

    trigger OnOpenPage()
    var
        SalesHeader: Record "Sales Header";
        NumberOfPrimaryKeyFields: Integer;
    begin
        if IsPrimaryKeyField(Database::"Sales Header", SalesHeader.FieldNo("No."), NumberOfPrimaryKeyFields) then
            Message('Field %1 is part of primary key, Number of primary key fields: %2', SalesHeader.FieldCaption("No."), NumberOfPrimaryKeyFields)
        else
            Message('Field %1 is not part of primary key.', SalesHeader.FieldCaption("No."));
    end;

    local procedure IsPrimaryKeyField(TableID: Integer; FieldID: Integer; var NumberOfPrimaryKeyFields: Integer): Boolean
    var
        RecRef: RecordRef;
        KeyRef: KeyRef;
    begin
        RecRef.Open(TableID);
        KeyRef := RecRef.KeyIndex(1);
        NumberOfPrimaryKeyFields := KeyRef.FieldCount;
        exit(IsKeyField(TableID, FieldID));
    end;

    local procedure IsKeyField(TableID: Integer; FieldID: Integer): Boolean
    var
        RecRef: RecordRef;
        FieldRef: FieldRef;
        KeyRef: KeyRef;
        KeyFieldCount: Integer;
    begin
        RecRef.Open(TableID);
        KeyRef := RecRef.KeyIndex(1);
        for KeyFieldCount := 1 to KeyRef.FieldCount do begin
            FieldRef := KeyRef.FieldIndex(KeyFieldCount);
            if FieldRef.Number = FieldID then
                exit(true);
        end;

        exit(false);
    end;
}

END

Hope this will help.

Thanks for reading.

ZHU