Tech Tip: Updated Utility method that returns the primary key's field id

PRODUCT: 4D | VERSION: 13.4 | PLATFORM: Mac & Win

Published On: July 22, 2014

Below is a method that returns the field ID of a table that has a primary key. First it confirms that the table does have a primary key, then it procedes to find the field that is set as the primary key.

C_LONGINT($0;$1;$table_id_l;$primary_key_field_id_l;$hasPK_l)

If (Count parameters>=1)
   $table_id_l:=$1

   Begin SQL
       SELECT TABLE_ID
       FROM _USER_CONSTRAINTS
       WHERE TABLE_ID = :$table_id_l
       AND CONSTRAINT_TYPE = 'P'
       INTO :$hasPK_l;
   End SQL

   If ($hasPK_l#0)
      Begin SQL
          SELECT COLUMN_ID
          FROM _USER_CONS_COLUMNS
          WHERE TABLE_ID = :$table_id_l
          INTO :$primary_key_field_id_l;
      End SQL
   Else
      $primary_key_field_id_l:=0
   End if
End if
$0:=$primary_key_field_id_l

Note: The Field's name can be returned by changing the resultant parameter ($0) to a text type and selecting COLUMN_NAME instead of COLUMN_ID from the second SQL querry.