Uniquekey and Tcolumnprop.unique on SQLite

BIZ TMS Aurelius
1

Hi


The uniquekey attribute and TcolumnProp.Unique argument of the Column attribute do not work on SQLite (Aurelius 3.4.1). Is this a known issue?
2

Hi,


No, that is not a known issue. Can you provide more details, what you mean exactly with "do not work"?
3

I am sorry, I will explain what I mean. I have a entity:

[Entity]

[Table('Opdrachtbonnen')]

[Id('FOpdrachtbonId', TIdGenerator.None)]

[UniqueKey('Opdrachtbonnr')]

TOpdrachtbon = class

private

    [Column('OpdrachtbonId', [TColumnProp.Required])]

    FOpdrachtbonId: TGuid;



    [Column('Opdrachtbonnr', [TColumnProp.Unique, TColumnProp.Required],15)]

    FOpdrachtbonnr: string;

    ..





As you can see, I tried both to use the [UniqueKey] attribute, and the TColumnProp.Unique to specify that there should be a unique index on the field Opdrachtbonnr. But when the database is updated, it does not make a (unique) index for the opdrachtbonnr field in the SQLite database. I would expect it would do that though, because the framework does not enforce this to be unique itself. So now I am able to make a TOpdrachtbon with a opdrachtbonnr that already exists.



I found out that when I create a new database it DOES create the a unique constraint. Only when I open a "old" database, that did not have this constraint yet, and update the database (using a call to TDatabasemanager.UpdateDatabase) the database is not properly altered to create this unique constraint.



I use SQLite with the FireDac connection in the latest version of Aurelius 3.4.01 and the latest version of Delphi (XE10.1 upd2). Tested it on a Win32 platform.



4

That's by design, see here:

http://www.tmssoftware.biz/business/aurelius/doc/web/schema_validation.htm
The relevant part is from the "Actions" subsection until the end ("Warnings" and "Errors")
5

Aaah, I understand. I never knew that you could validate the schema and get more info like that. I thought updatedatabase would fail in such cases. I will implement the validate method in my app and inform the user to refetch the complete database (that will rebuild a completely new database and download it to the device).

Thanks!