Error "Cannot add foreign key constraint"

Question

I am suffering the same "Cannot add foreign key constraint" that other folks around here.

The table client_partners contains a relationship between users. The error is raised at creating client_partners.

I have checked that users.id have the same type than client_partners.clientid and client_partners.partnerid: INT UNSIGNED NOT NULL.

The configuration options are:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

Users table definition:

-- -----------------------------------------------------
-- Table `users`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `users` ;

CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(50) NOT NULL,
  `password` VARCHAR(200) NOT NULL,
  `salt` VARCHAR(100) NULL,
  `firstname` VARCHAR(50) NOT NULL,
  `lastname` VARCHAR(50) NOT NULL,
  `email` VARCHAR(50) NOT NULL,
  `role` VARCHAR(10) NOT NULL DEFAULT 'supplier',
  `destination` VARCHAR(10) NULL,
  `supplierId` INT UNSIGNED NULL,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `username_UNIQUE` (`username` ASC),
  INDEX `fk_users_suppliers_idx` (`supplierId` ASC),
  INDEX `fk_users_roles_idx` (`role` ASC),
  INDEX `fk_users_destinations1_idx` (`destination` ASC),
  CONSTRAINT `fk_users_suppliers`
    FOREIGN KEY (`supplierId`)
    REFERENCES `suppliers` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_users_roles`
    FOREIGN KEY (`role`)
    REFERENCES `roles` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_users_destinations1`
    FOREIGN KEY (`destination`)
    REFERENCES `destinations` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

Client_partners table definition:

-- -----------------------------------------------------
-- Table `client_partners`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `client_partners` ;

CREATE TABLE IF NOT EXISTS `client_partners` (
  `clientid` INT UNSIGNED NOT NULL,
  `partnerid` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`clientid`, `partnerid`),
  CONSTRAINT `fk_client_partners_1`
    FOREIGN KEY (`clientid` , `partnerid`)
    REFERENCES `users` (`id` , `id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

The datatypes (except autoincrement) are the same. The referencing table (users) is created first. What am I missing?

Answer 1

CONSTRAINT `fk_client_partners_1`    
FOREIGN KEY (`clientid` , `partnerid`)
REFERENCES `users` (`id` , `id`)

here you are referencing to id of table users twice..it is not possible. try by removing one and create

If you want to reference to another foreign key you need to create it by using another name.