Search code examples
databasesqliteastrojs

Cannot use "foreignKeys" in Astro DB (foreign key mismatch)

I'm trying to use Astro DB and I get a very weird issue that makes no sense to me.

Here are the tables that are related to me issue (in /db/config.ts):

const Article = defineTable({
    columns: {
        id: column.number({ primaryKey: true }),
        author: column.number({ references: () => User.columns.id }),
        title: column.text(),
        date: column.date(),
        description: column.text(),
        text: column.text(),
    },
    indexes: [
        { on: ["id", "author"], unique: true }
    ]
});

const Theme = defineTable({
    columns: {
        theme: column.text({ primaryKey: true }),
    }
});

const ArticleThemes = defineTable({
    columns: {
        articleId: column.number(),
        theme: column.text(),
    },
    foreignKeys: [
        {
            columns: ["articleId", "theme"],
            references: () => [Article.columns.id, Theme.columns.theme],
        }
    ]
});

It's a basic ManyToMany relationship with an article that has one or more themes and each theme can be assigned to many articles.

Since the table "ArticleThemes" is made out of two foreign keys, I'm using the "foreignKeys" property when defining the table as suggested in the documentation (link here).

But it leads to a problem when seeding my database in my development environment (in /db/seeds.ts):

import { db, Article, Theme, ArticleThemes } from 'astro:db';

async function seedBlogArticles() {
    await db.insert(Article).values([
        {
            id: 1,
            author: 1,
            date: new Date(),
            text: "This is a very interesting blog post",
            title: "An interesting blog post",
            description: "Very interesting indeed",
        }
    ]);
}

async function seedThemes() {
    await db.insert(Theme).values([
            { theme: "svelte" },
        ]
    );
}


async function seedBlogArticleThemes() {
    await db.insert(ArticleThemes).values([
        {
            articleId: 1,
            theme: "svelte",
        },
    ])
}

// https://astro.build/db/seed
export default async function seed() {
    await seedThemes();
    await seedBlogArticles();
    await seedBlogArticleThemes();
}

Here is the error I get in the console:

[astro:db] New local database created.
[ERROR] [astro:db] Failed to seed database:
SQLITE_ERROR: foreign key mismatch - "ArticleThemes" referencing "Article"

NOTE: There is also a User table but it's not related to the problem. The error specifically occurs when calling seedBlogArticleThemes().

It was working before when I had a third column in ArticleThemes named "id" which was set as primary, and the other two columns had a simple reference (like I did for the author of an Article).

What did I do wrong?

Solution

  • You issue, I believe, is that you have have a single FKey that is trying to access two parent tables (in theory) but a Foreign key can only reference a single table and a column or columns therein).

    A composite foreign key constraint is one where the child and parent keys are both composite keys. ....

    Thus eventually the theme column cannot be accessed and a Foreign Key Mismatch (see demo)

    I believe that you need to define 2 Foreign Keys one for each table (article and theme).

    So something like:-

    const ArticleThemes = defineTable({
    columns: {
        articleId: column.number(),
        theme: column.text(),
    },
    foreignKeys: [
        {
            columns: ["articleId"],
            references: () => [Article.columns.id],
        },
        {
            columns: ["theme"],
            references: () => [Theme.columns.theme],
        }
    ]
});
    • Note the answer has been provided WITHOUT any knowledge of astrojs. Hence "something like"
    • You would probably want to make a composite primary key for the ArticleThemes table on the articleId and theme columns.
      • if not then you could end up with duplicate rows and thus meaningless/confusing associations/x-references.
    • You may also wish to make an index on the theme column of the articleThemes table (ensuring that it is not UNIQUE).

    Demo

    Perhaps consider the demo based upon converting the code into the approximate SQLite.

    • Note that to demonstrate the SQL is repeated the first showing the working version. The second repetition demonstrating what the code in the question appears to result in

    :-

    Part 1 (2 FK's as suggested)

    DROP TABLE IF EXISTS articleThemes;
DROP TABLE IF EXISTS theme;
DROP TABLE IF EXISTS article;
CREATE TABLE IF NOT EXISTS article (
    id INTEGER PRIMARY KEY /*   column.number({ primaryKey: true })*/,
    author INTEGER  /*: column.number({ references: () => User.columns.id }) */,
    title TEXT /*: column.text()*/,
    date TEXT /*: column.date()*/,
    description TEXT /*: column.text()*/,
    `text` TEXT /*: column.text()*/
);

CREATE TABLE IF NOT EXISTS theme (
    theme TEXT PRIMARY KEY
);
CREATE TABLE IF NOT EXISTS articleThemes (
    articleId INTEGER,
    theme TEXT,
    /*FOREIGN KEY (articleid,theme) REFERENCES article(id,theme)*/
    FOREIGN KEY (articleId) REFERENCES article(id)
    FOREIGN KEY (theme) REFERENCES theme(theme),
    PRIMARY KEY(articleId,theme)
);
INSERT OR IGNORE INTO article VALUES
    (1,'Fred','The Title','2024-01-01','Blah','blah blah blah'),
    (2,'Bert','The Title','2024-01-01','Blah','blah blah blah')
;
INSERT OR IGNORE INTO theme VALUES('Theme1'),('Theme2'),('Theme3');
INSERT OR IGNORE INTO articleThemes VALUES (1,'Theme1'),(1,'Theme1'),(2,'Theme2');
    • Note the 2 Foreign Keys (and the suggested Primary Key), otherwise the 2nd insert would be a duplicate and perhaps cause confusion.
    • also not the commented out foreign key as what would have been (as used in part 2)

    The results of part one is:-

    INSERT OR IGNORE INTO article VALUES
    (1,'Fred','The Title','2024-01-01','Blah','blah blah blah'),
    (2,'Bert','The Title','2024-01-01','Blah','blah blah blah')
> Affected rows: 2
> Time: 0.024s


INSERT OR IGNORE INTO theme VALUES('Theme1'),('Theme2'),('Theme3')
> Affected rows: 3
> Time: 0.024s


INSERT OR IGNORE INTO articleThemes VALUES (1,'Theme1'),(1,'Theme1'),(2,'Theme2')
> Affected rows: 2
> Time: 0.024s
    • i.e. data has been inserted as expected.

    Part 2 (single FK as interpreted)

    As per the code interpreted:-

    DROP TABLE IF EXISTS articleThemes;
DROP TABLE IF EXISTS theme;
DROP TABLE IF EXISTS article;
CREATE TABLE IF NOT EXISTS article (
    id INTEGER PRIMARY KEY /*   column.number({ primaryKey: true })*/,
    author INTEGER  /*: column.number({ references: () => User.columns.id }) */,
    title TEXT /*: column.text()*/,
    date TEXT /*: column.date()*/,
    description TEXT /*: column.text()*/,
    `text` TEXT /*: column.text()*/
);

CREATE TABLE IF NOT EXISTS theme (
    theme TEXT PRIMARY KEY
);
CREATE TABLE IF NOT EXISTS articleThemes (
    articleId INTEGER,
    theme TEXT,
    FOREIGN KEY (articleid,theme) REFERENCES article(id,theme)
    /*FOREIGN KEY (articleId) REFERENCES article(id)
    FOREIGN KEY (theme) REFERENCES theme(theme)*/
);

INSERT OR IGNORE INTO article VALUES
    (1,'Fred','The Title','2024-01-01','Blah','blah blah blah'),
    (2,'Bert','The Title','2024-01-01','Blah','blah blah blah')
;
    • Notice the single foreign key

    This results in:-

    CREATE TABLE IF NOT EXISTS articleThemes (
    articleId INTEGER,
    theme TEXT,
    FOREIGN KEY (articleid,theme) REFERENCES article(id,theme)
    /*FOREIGN KEY (articleId) REFERENCES article(id)
    FOREIGN KEY (theme) REFERENCES theme(theme)*/
)
>  OK
>  Time: 0.024s


INSERT OR IGNORE INTO article VALUES
    (1,'Fred','The Title','2024-01-01','Blah','blah blah blah'),
    (2,'Bert','The Title','2024-01-01','Blah','blah blah blah')
  >  foreign key mismatch - "articleThemes" referencing "article"
  >  Time: 0s
    • i.e. the theme column's non existence is NOT discovered at table creation BUT is then discovered when the attempt is made to insert into the article table and results in the FK mismatch error.

    Additional re comments.post on Github

    1. It does not really matter if you cannot use a composite primary key, a UNIQUE composite index will suffice and there is probably little if any difference.

    2. The fact that a primary key on a field/column named id also does not really matter. This is because that index will exist anyway unless the table defined using/as a WITHOUT ROWID table (which may not even catered for in AstroDb).

      1. That is, normal tables ALWAYS have a normally hidden rowid which can be considered as the super primary key/index. All that having the id field defined as the primary key effectively does is to unhide the rowid as the id is an alias of the rowid. The index will always exist (unless WITHOUT ROWID). There are a few quirka/nuances but those should not matter. You may wish to refer to https://www.sqlite.org/withoutrowid.html and/or https://sqlite.org/rowidtable.html

    3. the suggestion for an index on the theme column of the ArticleThhemes still holds, as this will negate a full scan of the composite index if access is via a theme rather than an article.