MySQL trying to restore two foreign keys returns errors-CodePudding

I have this scenario: I have those two tables:

CREATE TABLE sample_A (
  ID bigint(20) UNSIGNED NOT NULL,
  product varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE sample_B (
  ID bigint(20) UNSIGNED NOT NULL,
  ref_id_sample_A_id bigint(20) UNSIGNED NULL,
  ref_id_sample_A_ref_id bigint(20) UNSIGNED NULL,
  document_name varchar(300) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

and I was trying to restore a foreign key between ref_id_sample_A_id of table sample_B and ID of table sample_A but executing this instruction:

ALTER TABLE sample_B
  ADD CONSTRAINT fk_sample_B_ref_sample_A_id
    FOREIGN KEY (ref_id_sample_A_id) 
    REFERENCES sample_A(ID);

I obtain this error:

#1823 - Failed to add the foreign key constraint 'k3/fk_sample_B_ref_sample_A_id' to system tables

but I have no other foreign keys settled, neither informations if I query this:

SELECT * FROM information_schema.TABLE_CONSTRAINTS
    WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' 
    AND TABLE_NAME = 'sample_B';

I get empty result, and table simple_A is not a system table.... what I should do? Thanks in advance to all!

Cheers

CodePudding user response：

I tested your sample using MySQL 8.0.26, and got this error:

ERROR 1822 (HY000): Failed to add the foreign key constraint. Missing index for constraint 'fk_sample_B_ref_sample_A_id' in the referenced table 'sample_A'

This means the referenced column, sample_A.ID, is not valid as the referenced column of a foreign key, because it is not part of a key in that table.

The referenced column must be part of a key, and ideally should be a PRIMARY KEY or UNIQUE KEY.

You did not define ID or any other column as the PRIMARY KEY for the table sample_A.

CodePudding user response：

Solved right 2 sec ago anyway ... had to export dump data of the table, drop the table and recreate the table with all constraints in CREATE TABLE instruction then re-import all dump data and it worked. Thanks anyway to everyone @nbk and @Bill Karwin! :-)