How do I do a SQLight record update on a table with no primary key?

Question

I have the following table that does not have a primary key.

public class Person
{
  public string PersonId { get; set; }
  public string ConfirmationNumber { get; set; }
  public string Sid  { get; set; }
}

I insert a record with these values

Person p = new Person();
p.PersonId = 0;
p.ConfirmationNumber = "ABC-DEF-GHJ";
p.Sid = "19240";

await DatabaseAsync.InsertAsync(p);

I then need to update this record with these vaues

p.PersonId = 2047;
p.ConfirmationNumber = "ABC-DEF-GHJ";
p.Sid = "19240";

SQLight does not have an Update.With()

I thought about deleting the rec and re-inserting it with the new values but SQLight does not have a Delete.With() and I can't use Replace() since this table doesn't have a primary key.

Note: The combination of ConfirmationNumber and Sid does uniquely identify every record with no duplicates.

Answer 1

I have the following table that does not have a primary key.

Primary Key is a type of constraint that is used to apply one or more columns of a table to restrict to maintain data integrity. The primary key helps us to ensure that there is no duplicate record with the same token or ID. In a table when we define the primary key constraint to a particular column then our table stores all the records physically by the order of the primary key by which the data retrieval is very fast.

So, it is best practice to define the primary key for our tables. Edit: The Primary Key is used as an index and without indexes the dB has to do full table scans, rather than scan the index.

And since you use nuget sqlite-net-pcl, you can add a primary key for table Person as follows:

public class Person
{
  // add primary key `ID`
  [PrimaryKey, AutoIncrement]
  public int ID { get; set; }

  public string PersonId { get; set; }
  public string ConfirmationNumber { get; set; }
  public string Sid  { get; set; }
}

Note:

For more information about this, you can check official document .NET MAUI local databases.

And there is an official example about this, you can check it here: .NET MAUI - Local Database with SQLite.