Gorm Bug: SQLite foreign key not created on self-reference

I have this example One-To-Oone

type Category struct {
    gorm.Model

    Name        string  `json:"name" gorm:"type: not null; unique"`
    Description string  `json:"desciption"`
    Stocks      []Stock `gorm:"constraint:OnUpdate:CASCADE,OnDelete:CASCADE"`

    ParentID *uint
    Parent   *Category `gorm:"foreignkey:parent_id;constraint:OnUpdate:CASCADE,OnDelete:CASCADE;"`
}

I want category to have self-reference on parent_id,

The screenshot below shows that the foreingkey between category and parent_id doesn't exist: Gorm Bug: SQLite foreign key not created on self-reference

Note one-to-many works fine!

Comment From: github-actions[bot]

The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 30 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨

Comment From: zakaria-chahboun

i did a solution

// just to add a self-relation in category! (bug https://github.com/go-gorm/gorm/issues/5584)

SQL := `
PRAGMA foreign_keys = 0;

CREATE TABLE sqlitestudio_temp_table AS SELECT *
                                          FROM categories;

DROP TABLE categories;

CREATE TABLE categories (
    id          INTEGER,
    created_at  DATETIME,
    updated_at  DATETIME,
    deleted_at  DATETIME,
    name                 NOT NULL
                         UNIQUE,
    description TEXT,
    parent_id   INTEGER  CONSTRAINT fk_categories_categories REFERENCES categories (id) ON DELETE CASCADE
                                                                                        ON UPDATE CASCADE,
    PRIMARY KEY (
        id
    )
);

INSERT INTO categories (
                           id,
                           created_at,
                           updated_at,
                           deleted_at,
                           name,
                           description,
                           parent_id
                       )
                       SELECT id,
                              created_at,
                              updated_at,
                              deleted_at,
                              name,
                              description,
                              parent_id
                         FROM sqlitestudio_temp_table;

DROP TABLE sqlitestudio_temp_table;

CREATE INDEX idx_categories_deleted_at ON categories (
    deleted_at
);

PRAGMA foreign_keys = 1;
`

database.Exec(SQL)

Comment From: github-actions[bot]

Comment From: zakaria-chahboun

It's works! But it doesn't work with gorm-sqlite-cipher driver