0

I tried adding a Foreign Key using this query:

ALTER TABLE `StripeBilling`
        ADD CONSTRAINT `StripeBillingPatientRef`
         FOREIGN KEY (PatientRef)
          REFERENCES `Patient`(`PatientId`)
            ON DELETE SET NULL;

My tables mysql_dump is:

CREATE TABLE IF NOT EXISTS `StripeBilling` (
  ...
  `PatientRef` int(10) unsigned DEFAULT NULL,
  ...
) ENGINE=InnoDB AUTO_INCREMENT=10000000 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE `StripeBilling`
  ADD PRIMARY KEY (`StripeBillingId`), ADD KEY `StripeBillingAgentRef` (`AgentRef`), ADD KEY `PatientRef` (`PatientRef`);
ALTER TABLE `StripeBilling`
  MODIFY `StripeBillingId` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=10000000;
ALTER TABLE `StripeBilling`
ADD CONSTRAINT `StripeBillingAgentRef` FOREIGN KEY (`AgentRef`) REFERENCES `Agent` (`AgentId`) ON DELETE SET NULL;

And patient's table is:

CREATE TABLE IF NOT EXISTS `Patient` (
  `PatientId` int(10) unsigned NOT NULL,
  ...
) ENGINE=InnoDB AUTO_INCREMENT=10000000 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE `Patient`
  ADD PRIMARY KEY (`PatientId`), ADD UNIQUE KEY `PatientSerial` (`PatientSerial`);
ALTER TABLE `Patient`
  MODIFY `PatientId` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=10000000;

But MySQL returns an error:

Cannot add or update a child row: a foreign key constraint fails (newportal_enc.#sql-3863_9f, CONSTRAINT StripeBillingPatientRef FOREIGN KEY (PatientRef) REFERENCES Patient (PatientId) ON DELETE SET NULL)

I'm confused because table newportal_enc.#sql-3863_9f does not exist in my database.

This is not due to the presence of the PatientRef index, becouse after removing of this index my issue still exists.

Why am I getting this error and how can I solve it?

Lakremon
  • 787
  • 1
  • 8
  • 26
  • 2
    Possible duplicate of [Mysql error 1452 - Cannot add or update a child row: a foreign key constraint fails](http://stackoverflow.com/questions/1253459/mysql-error-1452-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fa) – code_dredd Aug 31 '16 at 11:44
  • No, it not same problem. Always, when I try find error 1452 usaly in question whos error in SQL query. But my query is right cos it run right in migration on same computers, but I have this problem onli on staging server. Also I try add FK for `StripeBilling` table, but MySQL return `#sql-3863_9f` table. # is sign of temp tables as long as I remember. – Lakremon Aug 31 '16 at 12:03
  • Make sure the columns are the *same* data type. I've run into PK/FK issues when data types are not exactly the same, including `int` vs `bigint`, string encodings (e.g. `utf-8` vs `latin1`), and so on. Please confirm whether this is the case or not. – code_dredd Aug 31 '16 at 13:09
  • I'm sure of course. Why both unsigned int(10). I will add more info today to show it not common case. – Lakremon Sep 01 '16 at 03:06
  • 1
    Please run `SHOW ENGINE INNODB STATUS` for the exact error message (you may need to do it as root). – Álvaro González Sep 02 '16 at 08:09
  • Thx, I'm find solution! Trouble whas in not exists primary keys in Patient table. – Lakremon Sep 02 '16 at 22:21

0 Answers0