DBMetal (SQLite): "Sequence contains more than one element" with multiple foreign keys

Question

When trying to create a C# file using DbMetal (as part of DbLinq), I get the following error:

DbMetal: Sequence contains more than one element

It's only appearing when I reference multiple foreign keys as part of my primary key. The following is the DDL for my table causing issues:

CREATE TABLE [QuestionChoice] 
(
    [QuestionaireID] INTEGER NOT NULL,
    [QuestionNumber] INTEGER NOT NULL,
    [ChoiceNumber] INTEGER NOT NULL,
    [Wording] VARCHAR
    (
        100
    )
    NOT NULL,
    PRIMARY KEY 
    (
        [ChoiceNumber],
        [QuestionNumber],
        [QuestionaireID]
    ),
    FOREIGN KEY 
    (
        [QuestionNumber],
        [QuestionaireID]
    )
    REFERENCES [Question]
    (
        [QuestionNumber],
        [QuestionaireID]
    )
)

The tool I'm using to setup my SQLite database is SQLite Studio. I set a table constraint to set the foreign keys.

If I set the foreign keys seperately (per item) instead of as a table constraint, the generated classes have multiple references to the Question table, causing multiple references and errors when trying to insert into the table.

Hi, this question might help: http://stackoverflow.com/questions/2761701/dbmetal-chokes-on-repeated-foreign-key-references-in-sqlite-any-ideas — andyp, Jul 15 '10 at 22:00
@andyp: This is the question I kept coming up with when Googling this, however I don't have a repeated foreign key here. I even went as far as re-compiling DbMetal with the provided patch, with no luck. — Will Eddins, Jul 15 '10 at 23:45
Why would you use any primary key in SQLite other than a single-column "INTEGER PRIMARY KEY NOT NULL"? Any other choice than that is not a *real* primary key for that database anyway. See the [SQLite docs](http://www.sqlite.org/lang_createtable.html#rowid) for more info. — Stephen Cleary, Jul 16 '10 at 01:59
I tried changing the QuestionChoice table to have only a single `INTEGER PRIMARY KEY`, and DbMetal is still complaining. This isn't the issue, it's the existance of 2 foreign keys throwing it off. — Will Eddins, Jul 16 '10 at 02:17

score 2 · Accepted Answer · answered Sep 19 '10 at 06:57

To solve the issue, I took Stephen Cleary's suggestion in the comments, and used a single INTEGER PRIMARY KEY for all tables. It appears that while SQLite may support multiple foreign keys, DBMetal chokes on the idea.

As a result, a foreign key to another table results in a single reference, and DBMetal handles everything appropriately.

DBMetal (SQLite): "Sequence contains more than one element" with multiple foreign keys

1 Answers1