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.
The error message itself showing there is a foreign key constraint error, which means you are deleting a parent table where the child table contains the Primary table identifier as a foreign key. To avoid this error, you need to delete child table records first and after that the parent table record.
The error comes when you are trying to add a row for which no matching row in in the other table. “Foreign key relationships involve a parent table that holds the central data values, and a child table with identical values pointing back to its parent. The FOREIGN KEY clause is specified in the child table.
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
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With