[typed_sql] Feature request: unique constraint on multiple columns

[Romain-Guillot]

Currently, the package only supports unique constraints on single fields.
Do you plan to add support for defining unique constraints across multiple columns (composite unique keys)?

[jonasfj]

There is already support for composite foreign keys, I see no reason there shouldn't be support for composite unique constraints.

I just haven't found a good way to define.

Option (A): @Unique(given: ...) on an field

abstract final class Person extends Row {
  @Unique(given: ['lastName'])
  String get firstName;

  String get lastName;
}

Then Table<Person> could have a Table<Person>.byfirstName(String lastName) method, for quickly doing lookups using this unique constraint.

Option (B): @UniqueKey([...]) on row-class

@UniqueKey(name: 'fullname', field: ['firstName', 'lastName'])
abstract final class Person extends Row {
  String get firstName;
  String get lastName;
}

Where the (optional) name field gives rise to Table<Person>.byFullname(String firstName, String lastName), for quickly doing lookups using the unique constraint.

---

I'm not sure I like the @Unique(given: [...]) in option (A); it's a bit weird that the unique constraint is defined with an annotation on the first field.

I'm also not sure I like the @UniqueKey annotation name in option (B)`. Maybe there is a better name.

Maybe, there are other approaches better than option (A) and (B). I suspect this is mainly blocked by making sure if we have good intuitive names for defining this. We could change @Unique to always be used on the row-class, but it'd be a bit sad, when in practice the annotations is most commonly used as an annotation on a single field.

But the solution for composite foreign-keys was to have both @References and @ForeignKey used as annotations on fields and row-classes, respectively.

---

side note: I'm currently on parental leave for a few months, with sufficient tests I might be able to take PRs, but bandwidth is limited for the next few months.

[isoos]

A few random thoughts:

• I think option A creates ambiguity, as it is not clear which field should have the annotation (or why the first should have it), B is unambiguous in that regard.
• While it is not necessarily a key, the fact that the combination of columns is unique along the the rows in the table, it could be used as such, so @UniqueKey may be just as good name as @UniqueContstraint or Unique (as otherwise we don't have any other uniqueness, do we?).
• The single-column unique constraint count be @UniqueColumn annotation, without any parameters. Or the @Unique could be on both row/class and column/field.