CODEWITHSUNDEEP
mysqlsqlforeign-keyssql-insert
smeeb
smeeb

Reputation: 29497

Unable to insert rows and getting foreign key constraint fails error

I have the following table:

CREATE TABLE IF NOT EXISTS profile_claim_ruling_tasks (
    profile_claim_ruling_task_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    account_id BIGINT UNSIGNED NOT NULL,
    profile_id BIGINT UNSIGNED NOT NULL,
    admin_task_status_id BIGINT UNSIGNED NOT NULL,
    profile_claim_ruling_task_ref_id VARCHAR(36) NOT NULL,
    profile_claim_ruling_task_requested_at DATETIME NOT NULL,

    CONSTRAINT pk_profile_claim_ruling_tasks PRIMARY KEY (profile_claim_ruling_task_id),
    CONSTRAINT fk_profile_claim_ruling_task_account_id FOREIGN KEY (account_id) REFERENCES accounts (account_id),
    CONSTRAINT fk_profile_claim_ruling_task_profile_id FOREIGN KEY (profile_id) REFERENCES accounts (profile_id),
    CONSTRAINT fk_profile_claim_ruling_task_admin_task_status_id FOREIGN KEY (admin_task_status_id) REFERENCES admin_task_statuses (admin_task_status_id),

    INDEX idx_profile_claim_ruling_tasks_admin_task_status_id(admin_task_status_id),

    CONSTRAINT uc_profile_claim_ruling_tasks_profile_claim_ruling_tasks_ref_id UNIQUE (profile_claim_ruling_task_ref_id)
);

When I do a SELECT * on accounts:

+------------+--------------------------------------+------------+
| account_id | account_ref_id                       | profile_id |
+------------+--------------------------------------+------------+
|          1 | 521ef2cb-01f9-49f3-a214-42e1514d7dc2 |          1 |
+------------+--------------------------------------+------------+

And when I do a SELECT * on profiles:

+------------+--------------------------------------+
| profile_id | profile_ref_id                       |
+------------+--------------------------------------+
|          2 | 1d8caa66-e080-4cc6-88ff-a063e576bafa |
|          3 | 619a7ec6-813a-41f0-a1f9-16289893df5d |
|          4 | c50ceb2f-49f0-4319-b115-0a1454593c46 |
|          1 | d6369f9b-b66a-468c-86f9-a7e0abc75b65 |
+------------+--------------------------------------+

So far, so good! But when I run the following insert:

INSERT INTO profile_claim_ruling_tasks (
  account_id,
  profile_id,
  admin_task_status_id,
  profile_claim_ruling_task_ref_id,
  profile_claim_ruling_task_requested_at
) VALUES (
  1,
  4,
  1,
  '4bed7334-e17b-462f-a7e6-454c3b2f5235',
  '2018-01-29 13:12:57'
);

I get the following error:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`myapp_db`.`profile_claim_ruling_tasks`, CONSTRAINT `fk_profile_claim_ruling_task_profile_id` FOREIGN KEY (`profile_id`) REFERENCES `accounts` (`profile_id`))

What's going on here?!

Upvotes: 1

Views: 1101

Answers (2)

Wilson Hauck
Wilson Hauck

Reputation: 2343

You appear to have an error in your first SHOW CREATE TABLE,

CONSTRAINT fk_profile_claim_ruling_task_profile_id FOREIGN KEY (profile_id) REFERENCES accounts (profile_id),

should be

CONSTRAINT fk_profile_claim_ruling_task_profile_id FOREIGN KEY (profile_id) REFERENCES profiles (profile_id),

IMO.

Upvotes: 0

Ravi
Ravi

Reputation: 31407

FOREIGN KEY (profile_id) REFERENCES accounts (profile_id)

You are trying to insert data with profile_id=4 in table profile_claim_ruling_tasks, which is referring to accounts (profile_id).

But, you don't have profile_id=4 in accounts table. You need to populate accounts table first to resolve this issue.

Upvotes: 5

Related Questions