Foreign key constraint failed -- after inserting item into child table and relaunching application

[lscheeler]

Description

Hi, I am stuck on an issue where I am receiving the error: FOREIGN KEY constraint failed after doing the following steps:

1. Launch program, successfully creates and populates tables
2. Insert a new item/row into the child table (successfully inserts row)
3. Quit program and re-launch
4. When re-launching, fails on createConnection with the FOREIGN KEY constraint error

Setup

Parent entity

    @OneToMany((type) => Child, (child) => child.parent, { cascadeInsert: true, cascadeUpdate: true, eager: true })
    public children: Child[];

Child entity

  @ManyToOne((type) => Parent, (parent) => parent.children, { cascadeInsert: true, cascadeUpdate: true })
  public parent: Parent;

Database connection

  const databaseOptions: ConnectionOptions = {
    type: 'sqlite',
    database: path.join(basePath, 'db.sqlite'),
    entities: [
      Parent,
      Child,
    ],
    logging: true,
    logger: 'advanced-console',
    synchronize: true,
  };
  const databaseConnection = await createConnection(databaseOptions);

Error message

(modified/removed proprietary code)

executing query:  PRAGMA foreign_key_list("child")
executing query:  PRAGMA index_info("sqlite_autoindex_parent_1")
executing query:  PRAGMA index_info("sqlite_autoindex_child_1")
executing query:  PRAGMA index_info("ind_565367d8c780b948e6b25a53a3")
executing query:  PRAGMA index_info("ind_951227b4282bfe94368dc54d5f")
executing query:  PRAGMA index_info("ind_f5ecae8af967aa5d59c45a98e6")
executing query:  PRAGMA index_info("sqlite_autoindex_parent_4")
executing query:  PRAGMA index_info("sqlite_autoindex_parent_3")
executing query:  PRAGMA index_info("sqlite_autoindex_parent_2")
executing query:  CREATE TABLE "temporary_child" (.... FOREIGN KEY("parentId") REFERENCES "parent"("id"))
executing query:  INSERT INTO "temporary_child"(... "parentId" FROM "child"
executing query:  DROP TABLE "child"
executing query:  ALTER TABLE "temporary_child" RENAME TO "child"
executing query:  CREATE TABLE "temporary_parent" ("id" varchar NOT NULL PRIMARY KEY, ...
executing query:  INSERT INTO "temporary_parent"("id", ... SELECT "id", .. FROM "user"
executing query:  DROP TABLE "parent"
query failed:  DROP TABLE "parent"
error: 
PlatformTools.ts:202
Error: SQLITE_CONSTRAINT: FOREIGN KEY constraint failed
PlatformTools.ts:202
executing query:  ROLLBACK

Questions

Does this have to do with the synchronize connection option? If so, I am not sure of how to get around that or the proper way to handle database schema on application re-launch.

[pleerock]

are you able to setup a git repo with reproduction of this problem, so we can debug your issue?

[lscheeler]

@pleerock Here is a simplified version of my code. Let me know if you have any issues

[pleerock]

This should be fixed in 0.1.9, can you please check it?

[lscheeler]

@pleerock Yay, this fixed it! Thanks!

[pleerock]

Thank you for the report!

[aigoncharov]

@pleerock I have the same issue for ManyToMany relations

[lscheeler]

@keenondrums He said the issue was actually a bug triggered from using auto generated uuid columns -> @PrimaryGeneratedColumn('uuid') If you are using this annotation in your entity, then that would be the cause. You can update to typeorm version 0.1.9 to get the bugfix

[aigoncharov]

@lscheeler sorry, I guess I misread it. My issue is a little different then. I'm on 1.0.9

export class EntityId {
  @PrimaryGeneratedColumn()
  id: number
}

@Entity()
export class Schema extends EntityId {
  @Column()
  name: string

  @Column({ type: 'integer' })
  seqNo: number

  @OneToMany(
    (type) => SchemaField,
    (field) => field.schema,
    {
      cascadeInsert: true,
      eager: true
    }
  )
  fields: SchemaField[]
}

@Entity()
export class SchemaField extends EntityId {
  @Column()
  name: string

  @Column({ type: 'integer' })
  type: SchemaFieldType

  @ManyToMany((type) => Validator)
  @JoinTable()
  constraints: Validator[]

  @ManyToOne(
    (type) => Schema,
    (schema) => schema.fields
  )
  schema: Schema
}

@Entity()
export class Validator extends EntityId {
  @Column()
  name: string

  @Column()
  validator: string

  @Column({
    transformer: {
      from: (val) => val.split(',').map(parseInt),
      to: (val) => val.join(',')
    },
    type: 'text'
  })
  fieldTypes: SchemaFieldType[]
}

primary keys of validator has changed: dropped - nothing; added - id
executing query:  CREATE TABLE "temporary_validator" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "name" varchar NOT NULL, "validator" varchar NOT NULL, "fieldTypes" text NOT NULL)
executing query:  INSERT INTO "temporary_validator"("id", "name", "validator", "fieldTypes") SELECT "id", "name", "validator", "fieldTypes" FROM "validator"
executing query:  DROP TABLE "validator"
executing query:  ALTER TABLE "temporary_validator" RENAME TO "validator"
primary keys of schema_field has changed: dropped - nothing; added - id
executing query:  CREATE TABLE "temporary_schema_field" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "name" varchar NOT NULL, "type" integer NOT NULL, "schemaId" integer, FOREIGN KEY("schemaId") REFERENCES "schema"("id"))
executing query:  INSERT INTO "temporary_schema_field"("id", "name", "type", "schemaId") SELECT "id", "name", "type", "schemaId" FROM "schema_field"
executing query:  DROP TABLE "schema_field"
executing query:  ALTER TABLE "temporary_schema_field" RENAME TO "schema_field"
primary keys of schema has changed: dropped - nothing; added - id
executing query:  CREATE TABLE "temporary_schema" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "name" varchar NOT NULL, "seqNo" integer NOT NULL)
executing query:  INSERT INTO "temporary_schema"("id", "name", "seqNo") SELECT "id", "name", "seqNo" FROM "schema"
executing query:  DROP TABLE "schema"
query failed:  DROP TABLE "schema"
error:  { Error: SQLITE_CONSTRAINT: FOREIGN KEY constraint failed errno: 19, code: 'SQLITE_CONSTRAINT' }
executing query:  ROLLBACK

[lscheeler]

@keenondrums hmm maybe it's from any use of @PrimaryGeneratedColumn()

[tim-hub]

@lscheeler sorry, I guess I misread it. My issue is a little different then. I'm on 1.0.9

export class EntityId {
  @PrimaryGeneratedColumn()
  id: number
}

@Entity()
export class Schema extends EntityId {
  @Column()
  name: string

  @Column({ type: 'integer' })
  seqNo: number

  @OneToMany(
    (type) => SchemaField,
    (field) => field.schema,
    {
      cascadeInsert: true,
      eager: true
    }
  )
  fields: SchemaField[]
}

@Entity()
export class SchemaField extends EntityId {
  @Column()
  name: string

  @Column({ type: 'integer' })
  type: SchemaFieldType

  @ManyToMany((type) => Validator)
  @JoinTable()
  constraints: Validator[]

  @ManyToOne(
    (type) => Schema,
    (schema) => schema.fields
  )
  schema: Schema
}

@Entity()
export class Validator extends EntityId {
  @Column()
  name: string

  @Column()
  validator: string

  @Column({
    transformer: {
      from: (val) => val.split(',').map(parseInt),
      to: (val) => val.join(',')
    },
    type: 'text'
  })
  fieldTypes: SchemaFieldType[]
}

primary keys of validator has changed: dropped - nothing; added - id
executing query:  CREATE TABLE "temporary_validator" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "name" varchar NOT NULL, "validator" varchar NOT NULL, "fieldTypes" text NOT NULL)
executing query:  INSERT INTO "temporary_validator"("id", "name", "validator", "fieldTypes") SELECT "id", "name", "validator", "fieldTypes" FROM "validator"
executing query:  DROP TABLE "validator"
executing query:  ALTER TABLE "temporary_validator" RENAME TO "validator"
primary keys of schema_field has changed: dropped - nothing; added - id
executing query:  CREATE TABLE "temporary_schema_field" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "name" varchar NOT NULL, "type" integer NOT NULL, "schemaId" integer, FOREIGN KEY("schemaId") REFERENCES "schema"("id"))
executing query:  INSERT INTO "temporary_schema_field"("id", "name", "type", "schemaId") SELECT "id", "name", "type", "schemaId" FROM "schema_field"
executing query:  DROP TABLE "schema_field"
executing query:  ALTER TABLE "temporary_schema_field" RENAME TO "schema_field"
primary keys of schema has changed: dropped - nothing; added - id
executing query:  CREATE TABLE "temporary_schema" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "name" varchar NOT NULL, "seqNo" integer NOT NULL)
executing query:  INSERT INTO "temporary_schema"("id", "name", "seqNo") SELECT "id", "name", "seqNo" FROM "schema"
executing query:  DROP TABLE "schema"
query failed:  DROP TABLE "schema"
error:  { Error: SQLITE_CONSTRAINT: FOREIGN KEY constraint failed errno: 19, code: 'SQLITE_CONSTRAINT' }
executing query:  ROLLBACK

does this solved?