I have used the composite index in another project and it works just fine, in the current one however, the unique index is skipped silently on creation, as i found out today when my permissiosn system failed (duplicate roles were allocated to a user during an incorrect data migration operation).
For reasons beyond my control, I am just using Ormlite to create my schema while developing, while actually all long term db-access functionality will be in dapper (hence the use of the dapper/SimpleCRUD KEY attribute, in case that might be an issue?) Are there any other known conditions that would cause the multi-column unique constraint to not be created (failing/skipped silently?)
The previous (working) project created a table based on a single class, whereas this new logic creates a ResourceToRole : BaseTable , as follows:
[CompositeIndex(true, nameof(ResourceId), nameof(RecordState))]
[Table(nameof(ResourceToRole))]
public class ResourceToRole : TableBase
{
[AutoIncrement]
[Key]
public long ResourceToRoleId { get; set; }
[ServiceStack.DataAnnotations.Required]
[References(typeof(Resource))]
public long ResourceId { get; set; }
[NotMapped] [Reference]
public Resource Resource { get; set; }
[ServiceStack.DataAnnotations.Required]
[References(typeof(AccessRole))]
public long AccessRoleId { get; set; }
[NotMapped] [Reference]
public AccessRole AccessRole { get; set; }
}
and
// Base table that underlies ALL system tables, so as to gain access to record state, concurrency checks etc
public abstract class TableBase : IDbRecordState, IDbRowVersion
{
/// <inheritdoc />
[ServiceStack.DataAnnotations.Required]
[StringLength(40)]
public string RecordState { get; set; }
/// <inheritdoc />
[ServiceStack.DataAnnotations.Required]
[IgnoreInsert] // is taken care of by MSSQL Server, must be excluded from Insert so that Dapper.SimpleCRUD library can work
[IgnoreUpdate] // is taken care of by MSSQL Server, must be excluded from Update so that Dapper.SimpleCRUD library can work
//public ulong RowVersion { get; set; }
[CustomField("RowVersion")] // We make ServiceStack.Ormlite write out the column creation as RowVersion/Timestamp, but Dapper/EF needs it to be a byte[]
public byte[] RowVersion { get; set; }
}