How to set a table field primary in design-time Firedac?

Question

I want to make a not-null non-auto-inc integer my primary key, but I am unable to do so in design time with Firedac. There is no parameter for the TIntegerfield that allows me to make it primary. There is also no parameter of the TFDTable where I can choose the primary field out of all available fields.

I get that it may be possible doing it in code & combining it with my design time table but that beats the whole purpose of doing it all in design time.

Earlier I did have a auto-inc ID in my table, and this was automatically set to primary key. I deleted this field now because I need another integer to be the primary. Also I can't find information about the primary key & TFDTable on the official Embacadero website.

Answer 1

It's best to experiment with this using a new table in your database and a minimal new Delphi project.

Update: See below for database DDL and Form's DFM.

You need to have your ID field marked as a primary key in your database.

After you've added an FDConnection and an FDTable to your project, select the FDTable's TableName from the drop down list. Then, click in the FDTable's IndexName field and you should find an automatically-named index on the table's Primary Key. Just select it so that the IndexName takes its value. That's all there is to it.

For the table created using the DDL below, the IndexName property of the FDTable appears as sqlite_autoindex_test_1

If you then dbl-click the FDTable and use the pop-up Fields editor to set up persistent fields on the FDTable and then select your ID field, you should find that if you examine its ProviderFlags, they should include pfInKey, which is what tells FireDAC to use the field as the table's primary key when generating the SQL to update it, do inserts, etc.

You should find that the ID field's Required field is autmatically set to True, btw.

If you want to supply the ID Field's value yourself when adding a new record, use the table's OnNewRecord to generate the ID value an assign it to the field.

DDL for test Sqlite database

create table test(
id int not null primary key,
AName nchar(12)
)

Project DFM extract

object Form2: TForm2
  object DBGrid1: TDBGrid
    DataSource = DataSource1
  end
  object DBNavigator1: TDBNavigator
    DataSource = DataSource1
  end
  object FDConnection1: TFDConnection
    Params.Strings = (
      'Database=D:\aaad7\sqlite\MADB1.sqlite'
      'DriverID=SQLite')
    Connected = True
    LoginPrompt = False
  end
  object DataSource1: TDataSource
    DataSet = FDTable1
  end
  object FDTable1: TFDTable
    IndexName = 'sqlite_autoindex_test_1'
    Connection = FDConnection1
    UpdateOptions.UpdateTableName = 'test'
    TableName = 'test'
    object FDTable1id: TIntegerField
      FieldName = 'id'
      Origin = 'id'
      ProviderFlags = [pfInUpdate, pfInWhere, pfInKey]
      Required = True
    end
    object FDTable1AName: TWideStringField
      FieldName = 'AName'
      Origin = 'AName'
      FixedChar = True
      Size = 12
    end
  end
end