Primary key support for more then one column - i.e. Composite key

[kamransaleem]

public class Image
{
[PrimaryKey]
public int ItemId { get; set; }

    [PrimaryKey]
    public int ImageId { get; set; }

    public string Src { get; set; }

    public int Width { get; set; }

    public int Height { get; set; }

    public byte[] ImageBinary { get; set; }
}

Get the following error:

SQLite.SQLiteException was unhandled by user code
HResult=-2146233088
Message=table "Image" has more than one primary key
Source=MyPocket
StackTrace:
at SQLite.SQLite3.Prepare2(IntPtr db, String query) in c:\dev\tfs\MyPocket\MyPocket\SQLite.cs:line 2392
at SQLite.SQLiteCommand.Prepare() in c:\dev\tfs\MyPocket\MyPocket\SQLite.cs:line 1699
at SQLite.SQLiteCommand.ExecuteNonQuery() in c:\dev\tfs\MyPocket\MyPocket\SQLite.cs:line 1573
at SQLite.SQLiteConnection.Execute(String query, Object[] args) in c:\dev\tfs\MyPocket\MyPocket\SQLite.cs:line 467
at SQLite.SQLiteConnection.CreateTable(Type ty) in c:\dev\tfs\MyPocket\MyPocket\SQLite.cs:line 318
at SQLite.SQLiteConnection.CreateTableT in c:\dev\tfs\MyPocket\MyPocket\SQLite.cs:line 288
at MyPocket.Data.SQLitePocketRepository..ctor(String dbPath) in c:\dev\tfs\MyPocket\MyPocket\Data\SQLitePocketRepository.cs:line 24
at MyPocket.View.HomePage.d__0.MoveNext() in c:\dev\tfs\MyPocket\MyPocket\View\HomePage.xaml.cs:line 112
InnerException:

[georgepiva]

As composite key are not supported, I would like to know if there is any work around for it or if there are some best practices to follow in order to handle situations where a composite key appears to be necessary.

Source:
http://stackoverflow.com/questions/11105487/sqlite-net-and-foreign-keys

[softlion]

i created a patch

[JonnyWideFoot]

@softlion - Can I ask where you put the patch - I'd love support for composite primary keys. Thanks, Jon

[softlion]

#175

[SeymourRu]

...well?
May be it`s a good time to fix it?

[softlion]

...well?
May be it`s a good time to fix it?

It’s fixed since 3 years in my rewrite there https://github.com/softlion/SQLite.Net-PCL2

[SeymourRu]

...well?
May be it`s a good time to fix it?

It’s fixed since 3 years in my rewrite there https://github.com/softlion/SQLite.Net-PCL2

Yeah, I`ve seen this, thank you!
But still this package is not fixed yet and this is relatively simple feature.

[softlion]

What's fantastic, is that the task is marked as "up for grab".
Man, the pull request has 3 years now.
I won't update it anyway.

[flottokarotto]

Is there any chance to have this feature in the next 2 years?

[softlion]

Try my fork Le jeu. 18 août 2022 à 14:39, Florian Fischer ***@***.***> a écrit :

Is there any chance to have this feature in the next 2 years? — Message ID: ***@***.***>

-- Benjamin Mayrargue Vapolia.fr +33682227850 vapolia.fr <http://www.vapolia.fr>/ Skype/WhatsApp/Slack/Discord/Zoom/Teams/Meets/...

[tentom]

Adding composite key manually seems to work as a workaround for anyone driving here.

var cmd = connection.CreateCommand(
                "CREATE TABLE IF NOT EXISTS " + new TableMapping(typeof(ChatMessageDb)).TableName + " (" +
                " " + nameof(ChatMessageDb.ChatType) + " integer not null, " +
                " " + nameof(ChatMessageDb.Id) + " integer not null, " +
                " primary key (" + nameof(ChatMessageDb.ChatType) + ", " + nameof(ChatMessageDb.Id) + ")" +
                ");"
                );
            cmd.ExecuteNonQuery();

With the ChatMessageDb like this:

    public class DbRecord
    {
        public bool IsDeleted { get; set; }
        public DateTime UpdatedUtc { get; set; }
    }
    public class ChatMessageDb : DbRecord
    {
        public const short ChatType_Chat = 1;

        [PrimaryKey]
        public short ChatType { get; set; }
        [PrimaryKey]
        public int Id { get; set; }

        public int ContactUserId { get; set; }
        public bool IsSent { get; set; }
        public string Content { get; set; }
        public DateTime SentDateTime { get; set; }

    }

This was also mentioned in #1101

[softlion]

@tentom why do that when a version that does natively support composite primary keys is available ?
https://github.com/softlion/SQLite.Net-PCL2

[tentom]

Because it is a quick fix and workaround.

But I will take a look at that library as well.