With TypeORM, `SQLITE_CONSTRAINT: FOREIGN KEY constraint failed` when adding a column to an entity

user_420217 0
Viewed 8821

I'm using TypeORM as a TypeScript ORM library, with a SQLite database.

I've got a TypeORM entity, called Photo with a @OneToOne relationship with another entity, called PhotoMetadata.

Photo.ts:

import {
  Entity,
  Column,
  PrimaryGeneratedColumn,
  OneToOne,
  BaseEntity,
} from 'typeorm';

import PhotoMetadata from './PhotoMetadata';

@Entity()
export default class Photo extends BaseEntity {
  @PrimaryGeneratedColumn()
  public id: number;

  @Column({ length: 100 })
  public name: string;

  @OneToOne(
    () => PhotoMetadata,
    (photoMetadata) => photoMetadata.photo,
    { cascade: true },
  )
  metadata: PhotoMetadata;
}

And here is PhotoMetadata.ts:

import {
  Entity,
  Column,
  PrimaryGeneratedColumn,
  OneToOne,
  JoinColumn,
} from 'typeorm';

import Photo from './Photo';

@Entity()
export default class PhotoMetadata {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  comment: string;

  @OneToOne(
    () => Photo,
    (photo) => photo.metadata,
  )
  @JoinColumn()
  photo: Photo;
}

When I add a column to Photo, like:

  @Column({ nullable: true })
  test: string;

Then run the app, with logging enabled, I get:

query: BEGIN TRANSACTION
query: SELECT * FROM "sqlite_master" WHERE "type" = 'table' AND "name" IN ('photo_metadata', 'photo', 'user')
query: SELECT * FROM "sqlite_master" WHERE "type" = 'index' AND "tbl_name" IN ('photo_metadata', 'photo', 'user')
query: PRAGMA table_info("user")
query: PRAGMA index_list("user")
query: PRAGMA foreign_key_list("user")
query: PRAGMA table_info("photo")
query: PRAGMA index_list("photo")
query: PRAGMA foreign_key_list("photo")
query: PRAGMA table_info("photo_metadata")
query: PRAGMA index_list("photo_metadata")
query: PRAGMA foreign_key_list("photo_metadata")
query: PRAGMA index_info("sqlite_autoindex_photo_metadata_1")
query: SELECT * FROM "sqlite_master" WHERE "type" = 'table' AND "name" = 'typeorm_metadata'
query: CREATE TABLE "temporary_photo_metadata" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "comment" varchar NOT NULL, "photoId" integer, CONSTRAINT "UQ_99f01ed52303cc16139d69f7464" UNIQUE ("photoId"), CONSTRAINT "FK_99f01ed52303cc16139d69f7464" FOREIGN KEY ("photoId") REFERENCES "photo" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION)
query: INSERT INTO "temporary_photo_metadata"("id", "comment", "photoId") SELECT "id", "comment", "photoId" FROM "photo_metadata"
query: DROP TABLE "photo_metadata"
query: ALTER TABLE "temporary_photo_metadata" RENAME TO "photo_metadata"
query: CREATE TABLE "temporary_photo" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar(100) NOT NULL)
query: INSERT INTO "temporary_photo"("id", "name") SELECT "id", "name" FROM "photo"
query: DROP TABLE "photo"
query failed: DROP TABLE "photo"
error: [Error: SQLITE_CONSTRAINT: FOREIGN KEY constraint failed] {
  errno: 19,
  code: 'SQLITE_CONSTRAINT'
}
query: ROLLBACK

How can I fix this issue? It seems to fail dropping the Photo table that I modified, because of the foreign key.

typeorm typescript orm sqlite javascript
2 Answers

I tried using TypeORM migrations to do this, but I encountered the same problem.

I then learned the following from this comment:

const connection = await createConnection();

await connection.query('PRAGMA foreign_keys=OFF');
await connection.synchronize();
await connection.query('PRAGMA foreign_keys=ON');

Or if you want to use migrations instead, then from this comment:

await connection.query("PRAGMA foreign_keys=OFF;");
await connection.runMigrations();
await connection.query("PRAGMA foreign_keys=ON;");

In either case, you need to set synchronize: false in your ormconfig.json.

user_4202170

The problem may occurs when you already have some data in your database and you add new Entities. I've tried the two solution here but didn't work. But one thing worked for me: just drop the Table before open a new connection (Open an connection, drop table, close connection, open another and do what u and to do). Example:

createConnection({
type: "sqlite",
database: "datateste.sqlite",
entities: [
    User,
    Auditory,
    Goal,
    Supervised,
    Supervisor
],
synchronize: true,
logging: false}).then(async (e) => {
await e.dropDatabase()
await e.close()}).then(() => {
createConnection({
    type: "sqlite",
    database: "datateste.sqlite",
    entities: [
        User,
        Auditory,
        Goal,
        Supervised,
        Supervisor
    ],
    synchronize: true,
    logging: false
}).then(async connection => {
    // here you do what you want
}).catch(error => console.log(error));

})

user_109227640
Related