Bonjour,

Je bloque sur un pb de clés étrangères.

Le code de mes tables :

-- -----------------------------------------------------
-- Schema annonces
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `annonces` ;

-- -----------------------------------------------------
-- Schema annonces
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `annonces` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ;
USE `annonces` ;

-- -----------------------------------------------------
-- Table `annonces`.`department`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `annonces`.`department` ;

CREATE TABLE IF NOT EXISTS `annonces`.`department` (
  `dep_id` INT NOT NULL AUTO_INCREMENT,
  `dep_name` VARCHAR(45) NULL,
  PRIMARY KEY (`dep_id`))
ENGINE = InnoDB;

CREATE UNIQUE INDEX `dep_name_UNIQUE` ON `annonces`.`department` (`dep_name` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `annonces`.`city`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `annonces`.`city` ;

CREATE TABLE IF NOT EXISTS `annonces`.`city` (
  `city_id` INT NOT NULL AUTO_INCREMENT,
  `city_name` VARCHAR(255) NOT NULL,
  `city_dep` INT NOT NULL,
  PRIMARY KEY (`city_id`),
  CONSTRAINT `city_dep`
    FOREIGN KEY (`city_dep`)
    REFERENCES `annonces`.`department` (`dep_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE INDEX `city_dep_idx` ON `annonces`.`city` (`city_dep` ASC) VISIBLE;


-- -----------------------------------------------------
-- Table `annonces`.`user`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `annonces`.`user` ;

CREATE TABLE IF NOT EXISTS `annonces`.`user` (
  `user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_name` VARCHAR(100) NOT NULL,
  `user_email` VARCHAR(100) NOT NULL,
  `user_password` VARCHAR(255) NOT NULL,
  `user_department` INT NULL,
  `user_city` INT NULL,
  `user_phone` VARCHAR(10) NULL,
  `user_active` TINYINT NULL DEFAULT 0,
  PRIMARY KEY (`user_id`),
  CONSTRAINT `user_department`
    FOREIGN KEY (`user_department`)
    REFERENCES `annonces`.`city` (`city_dep`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `user_city`
    FOREIGN KEY (`user_city`)
    REFERENCES `annonces`.`city` (`city_id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE UNIQUE INDEX `user_name_UNIQUE` ON `annonces`.`user` (`user_name` ASC) VISIBLE;

CREATE UNIQUE INDEX `user_email_UNIQUE` ON `annonces`.`user` (`user_email` ASC) VISIBLE;

CREATE INDEX `user_department_idx` ON `annonces`.`user` (`user_department` ASC) VISIBLE;

CREATE INDEX `user_city_idx` ON `annonces`.`user` (`user_city` ASC) VISIBLE;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- -----------------------------------------------------
-- Data for table `annonces`.`department`
-- -----------------------------------------------------
START TRANSACTION;
USE `annonces`;
INSERT INTO `annonces`.`department` (`dep_id`, `dep_name`) VALUES (1, 'Tarn et Garonne');
INSERT INTO `annonces`.`department` (`dep_id`, `dep_name`) VALUES (2, 'Haut Rhin');

COMMIT;


-- -----------------------------------------------------
-- Data for table `annonces`.`city`
-- -----------------------------------------------------
START TRANSACTION;
USE `annonces`;
INSERT INTO `annonces`.`city` (`city_id`, `city_name`, `city_dep`) VALUES (1, 'Montauban', 1);
INSERT INTO `annonces`.`city` (`city_id`, `city_name`, `city_dep`) VALUES (2, 'Caussade', 1);
INSERT INTO `annonces`.`city` (`city_id`, `city_name`, `city_dep`) VALUES (3, 'Mulhouse', 2);
INSERT INTO `annonces`.`city` (`city_id`, `city_name`, `city_dep`) VALUES (4, 'Colmar', 2);

COMMIT;

• J'ai bien une clé étrangère "city_dep" dans la table city qui dépend de department.dep_id : quand je supprime un département dans la table department, les entrées liées dans la table city sont bien supprimées.
• J'ai une clé étrangère "user_dep" dans la table user qui dépend de city.dep_id. J'ai une seconde clé étrangère "user_city" dans la table user qui dépend de city.city_id.

Dans phpmyadmin, quand j'essaye d'ajouter un nouvel user, user_dep n'est-il pas censé me proposer uniquement des départements ( city.city_dep) ?

Au lieu de celà, user_dep me propose les valeurs de city.city_id. Je ne comprends pas mon erreur.

merci pour votre aide et bonne journée.