CODEWITHSUNDEEP
sqlsqlite
Node.JS
Node.JS

Reputation: 1570

Insert into table fails because of foreign key constraint

I am trying to insert into the "ProjectCategoryRelationship" table and I get an error saying foreign key constraint failed. I appreciate any help or hint.

Project, Category, ProjectCategoryRelationship tables:

CREATE TABLE "Project" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Project" PRIMARY KEY AUTOINCREMENT,
    "Title" varchar(256) NULL,
    "Description" text NULL,
    "UserId" INTEGER NULL,
    "CreatedOn" TEXT NOT NULL,
    CONSTRAINT "FK_Project_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
)

CREATE TABLE "Category" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_Category" PRIMARY KEY AUTOINCREMENT,
    "Name" varchar(256) NULL
)

CREATE TABLE "ProjectCategoryRelationship" (
    "ProjectId" INTEGER NOT NULL,
    "CategoryId" INTEGER NOT NULL,
    "Id" INTEGER NOT NULL,
    CONSTRAINT "PK_ProjectCategoryRelationship" PRIMARY KEY ("ProjectId", "CategoryId"),
    CONSTRAINT "FK_ProjectCategoryRelationship_Category_CategoryId" FOREIGN KEY ("CategoryId") REFERENCES "Category" ("Id") ON DELETE CASCADE,
    CONSTRAINT "FK_ProjectCategoryRelationship_Project_CategoryId" FOREIGN KEY ("CategoryId") REFERENCES "Project" ("Id") ON DELETE CASCADE
)

There is a Category with Id of 1:

> SELECT * from Category
Id  Name
1   java

There is a Project with Id of 5:

> SELECT * from Project
Id  Title   Description UserId  CreatedOn
5   Title   Description 1   0001-01-01 00:00:00+00:00

Insert new row to ProjectCategoryRelationship fails:

INSERT INTO "main"."ProjectCategoryRelationship"
("ProjectId", "CategoryId", "Id")
VALUES (5, 1, 1);

Result: FOREIGN KEY constraint failed
At line 1:
INSERT INTO "main"."ProjectCategoryRelationship"
("ProjectId", "CategoryId", "Id")
VALUES (5, 1, 1);

Upvotes: 0

Views: 420

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

Your second foreign key reference uses the wrong column:

CONSTRAINT "FK_ProjectCategoryRelationship_Project_CategoryId" FOREIGN KEY ("CategoryId") REFERENCES "Project" ("Id") ON DELETE CASCADE
----------------------------------------------------------------------------^

It should be ProjectId.

I would also discourage you from using double quotes on column and table names.

Upvotes: 2

Related Questions