Add support for making entity field(s) as unique key and composite unique keys for SQL DB

[daneshk]

Summary

The current bal persist design doesn't support making entity field(s) unique keys and composite unique keys. So there is no way to mark entity fields as unique keys. We need to support this for the DB introspection.

[daneshk]

The unique indexes and unique constraints are two different elements to consider when we use relational databases.

• Unique Indexes - Used to improve query performance by allowing for faster lookups on certain columns or fields. These indexes enforce a uniqueness restriction on certain fields so that all values stored within those fields must be unique from other values in the same column. This means that if you search for an entry within the indexed field, it will quickly retrieve the exact value stored within it instead of having to perform a full table scan.

• Unique Constraints - Used to enforce data integrity by ensuring that no duplicate values can be inserted into a database. Unlike Unique Indexes, Unique Constraints don’t just index the column – they prevent duplicate values from being entered into a table altogether.

If you want better performance for queries then using a Unique Index would be best since it can help reduce processing time by providing quicker lookups in specified fields; however, if your main priority is data integrity then opting for a Unique Constraint would be best since it prevents any potential data inconsistency issues by making sure only valid entries can be added.

[daneshk]

Check different other implementations,

• Prisma - has @unique and @@unique annotations mark the fields as unique
  • @unique - used to mark single unique field
  • @@unique - used to mark composite unique fields

Prisma doesn't differentiate unique indexes vs unique constraints. AFAIU, they decides whether to create indexes or constraints based on the DB https://www.prisma.io/docs/orm/reference/prisma-schema-reference#unique

• Hibernate/JPA - has support in table level and column level to mark fields as unique
  • @Column(unique = true) - used to mark single unique field
  • @Table(uniqueConstraints = {@UniqueConstraint(columnNames = {"mask", "group"})}) - used to mark composite unique fields
  • @Table(indexes = @Index(columnList = "firstName")) - used to create index column
  • @Index(name = "mulitIndex1", columnList = "firstName, lastName") - used to create composite keys
  • @Index(name = "uniqueIndex", columnList = "firstName", unique = true) - index uniqueness

https://www.baeldung.com/jpa-unique-constraints https://www.baeldung.com/jpa-indexes

• Microsoft EF - Has support in class level annotation to define the indexes(single/composite) in the class like below
  • [Index(nameof(Url))] - defines single index
  • [Index(nameof(FirstName), nameof(LastName))] - defines composite index
  • [Index(nameof(Url), IsUnique = true)] - defines unique index

https://learn.microsoft.com/en-us/ef/core/modeling/indexes?tabs=data-annotations

• GORM - Has support to create database index with tag index, uniqueIndex
  • gorm:"index:idx_name,unique", gorm:"uniqueIndex" - creates unique constraint
  • gorm:"index" - creates index
  • gorm:"index:idx_member" - Use the same index name to define composite indexes
  • gorm:"index:idx_id;index:idx_oid,unique" attaches multiple indexes to the same field

https://gorm.io/docs/indexes.html

[daneshk]

Design consideration

• The entity field can be marked as index or unique index. Not very clear about unique index vs unique constraint. Though there are syntax differences in defining them, both theoretically do the same thing.
• Two or more entity fields can be grouped together to form a composite unique.
• One entity field can be included in one or more composite groups.

Proposed Design

Based on the above, the initial proposed solution as follows, In the persist.sql model, we introduce new annotations called Index and Unique Index like below,

# Marks the entity field as an index field. 
public type IndexConfig record {|
    # The name used to define the index. This is required to group the fields for the composite index
    # If the field is mapped to two or more composite index groups, index names are listed in an array.
    string|string[]? name = ();
|};

# The Annotation indicates the index field in the `bal persist` model type.
 public annotation IndexConfig Index on record field;

# The Annotation indicates the unique index field in the `bal persist` model type.
 public annotation IndexConfig UniqueIndex on record field;

So developer can give the mapping DB table name and column names for the record and record field using Map annotation in the data model definition as follows,

• For single index and unique index fields

  
  import ballerina/persist as _;
  import ballerinax/persist.sql;

type Doctor record {| readonly int id; @sql:Index string name; string specialty; @sql:UniqueIndex string phoneNumber; Appointment[] appointment; |};

* For composite index and unique index fields
```ballerina
import ballerina/persist as _;
import ballerinax/persist.sql;

#  As the index names of both `name` and `specialty` and the same, they form a composite index.
type Doctor record {|
    readonly int id;
    @sql:Index("idx_id")
    string name;
    @sql:Index("idx_id")
    string specialty;
    string phoneNumber;
    Appointment[] appointment;
|};

• For fields included in two different composite indexes

  
  import ballerina/persist as _;
  import ballerinax/persist.sql;

Here the name field is included in two different composite indexes.

Ballerina doesn't allow the addition of the same annotation multiple times to the same entity fields. Hence index names are recorded as string array values.

type Doctor record {| readonly int id; @sql:Index(["idx_specialty", "idx_name"]) string name; @sql:Index("idx_specialty") string specialty; @sql:Index("idx_name") string phoneNumber; Appointment[] appointment; |};

[sameerajayasoma]

IMO, the ideal solution is this.

#  Here the name field is included in two different composite indexes.
type Doctor record {|
    readonly int id;
    @sql:Index {name: "idx_specialty" }
    @sql:Index {name: "idx_name" }
    string name;
    @sql:Index {name: "idx_specialty"}
    string specialty;
    @sql:Index {name: "idx_name"}
    string phoneNumber;
    Appointment[] appointment;
|};

But, you cannot declare the same annotation twice for a single attachment point. On the other hand, a table can have one or more indexes and each index has a name. With that definition, I believe the following should work. I prefer name instead of names.

#  Here the name field is included in two different composite indexes.
type Doctor record {|
    readonly int id;
    @sql:Index {name: ["idx_name", "idx_specialty"] }
    string name;
    @sql:Index {name: "idx_specialty"}
    string specialty;
    @sql:Index {name: "idx_name"}
    string phoneNumber;
    Appointment[] appointment;
|};

[daneshk]

This feature is implemented. Hence closing the issue