Geek Answers Handbook

Error code: 1822. Failed to add foreign key constant. Missing index for restriction

I found some error streams. But all solutions do not work for me.

I created 2 user table tables and one for articles. Now I want to save the user who created the article and who the last modifier is.

CREATE TABLE IF NOT EXISTS `testDb`.`users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `nickname` VARCHAR(255) NULL,
  `first_name` VARCHAR(255) NULL,
  `last_name` VARCHAR(255) NULL,
  `e_mail` VARCHAR(255) NOT NULL,
  `activated` TINYINT(1) NOT NULL DEFAULT 0,
  `birth_date` DATE NULL,
  `locked` TINYINT(1) NOT NULL DEFAULT 0,
  `locked_date_time` DATETIME NULL,
  `street` VARCHAR(255) NULL,
  `street_number` VARCHAR(255) NULL,
  `city` VARCHAR(255) NULL,
  `postal_code` VARCHAR(255) NULL,
  `country` VARCHAR(255) NULL,
  `phone` VARCHAR(255) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `user_id_UNIQUE` (`id` ASC)
)
ENGINE = InnoDB
AUTO_INCREMENT = 1;


CREATE TABLE IF NOT EXISTS `testDb`.`articles` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL,
  `description` VARCHAR(255) NULL,
  `create_user` INT ZEROFILL NOT NULL,
  `create_date_time` DATETIME NULL,
  `last_modifie_user` INT ZEROFILL NOT NULL,
  `last_modifie_date_time` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `article_id_UNIQUE` (`id` ASC),
  INDEX `fk_articles_users1_idx` (`create_user` ASC),
  INDEX `fk_articles_users2_idx` (`last_modifie_user` ASC)
)
ENGINE = InnoDB
AUTO_INCREMENT = 1;


ALTER TABLE `testDb`.`articles`
  ADD CONSTRAINT `fk_articles_users1`
    FOREIGN KEY (`create_user`)
    REFERENCES `testDb`.`users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_articles_users2`
    FOREIGN KEY (`last_modifie_user`)
    REFERENCES `testDb`.`users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;

I get the following error, but I did not understand why I should have an index for this.

Error code: 1822. Failed to add foreign key constant. Invalid index for constraint "fk_articles_users1" in users reference table

I activated

SHOW ENGINE innodb STATUS;

but it does not show any errors.

+4
source share
4 answers

create_user INT UNSIGNED ZEROFILL id INT, . .

, , varchar. , VARCHAR (10) VARCHAR (20) .

, .

ZEROFILL , , .

+7

, Data Type , , .

, :

  • Zero Fill
  • Not Null
  • Unsigned
  • Binary
+2

alter table - . articles :

CREATE TABLE IF NOT EXISTS `articles` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NULL,
  `description` VARCHAR(255) NULL,
  `create_user` INT ZEROFILL NOT NULL REFERENCES users(id),
  `create_date_time` DATETIME NULL,
  `last_modifie_user` INT ZEROFILL NOT NULL REFERENCES users(id),
  `last_modifie_date_time` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_articles_users1_idx` (`create_user` ASC),
  INDEX `fk_articles_users2_idx` (`last_modifie_user` ASC)
)
ENGINE = InnoDB
AUTO_INCREMENT = 1;

- SQL.

+1

SHOW FULL COLUMNS FROM table_name, Collation, , accounts name

mysql> SHOW FULL COLUMNS FROM accounts;
+----------+--------------+-------------------+------+-----+---------+----------+
| Field    | Type         | Collation         | Null | Key | Default | Extra    |
+----------+--------------+-------------------+------+-----+---------+----------|
| id       | int(11)      | NULL              | NO   | PRI | NULL    | auto_inc |
| name     | varchar(255) | utf8_bin          | YES  |     | NULL    |          |
| email    | varchar(255) | latin1_swedish_ci | YES  |     | NULL    |          |
...

.

ALTER TABLE t1 MODIFY
    col1 VARCHAR(5)
      CHARACTER SET latin1
      COLLATE latin1_swedish_ci;

.

0

More articles:


All Articles