mysql - 多对多关系实体双重间接外键问题

Question

我的表之间有以下关系：

CREATE TABLE `class`  (
  `id` int(0) NOT NULL,
  `grade` varchar(255) NOT NULL COMMENT 'first, second, etc (grade)',
  `province` varchar(255) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB ROW_FORMAT = Dynamic;

CREATE TABLE `class_province`  (
  `id` int(0) NOT NULL,
  `class` int(0) NOT NULL,
  `province` int(0) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `fk_nm_class`(`class`) USING BTREE,
  INDEX `fk_nm_province`(`province`) USING BTREE,
  CONSTRAINT `fk_nm_class` FOREIGN KEY (`class`) REFERENCES `class` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_nm_province` FOREIGN KEY (`province`) REFERENCES `province` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB ROW_FORMAT = Dynamic;

CREATE TABLE `class_schooltype`  (
  `id` int(0) NOT NULL,
  `class` int(0) NOT NULL,
  `schooltype` int(0) NOT NULL,
  INDEX `fk_nm_schooltype`(`class`) USING BTREE,
  CONSTRAINT `fk_nm_classschooltype` FOREIGN KEY (`class`) REFERENCES `class` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_nm_schooltype` FOREIGN KEY (`class`) REFERENCES `school_type` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB ROW_FORMAT = Dynamic;

CREATE TABLE `country`  (
  `id` int(0) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB ROW_FORMAT = Dynamic;

CREATE TABLE `province`  (
  `id` int(0) NOT NULL,
  `name` varchar(255) NOT NULL,
  `country` int(0) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `fk_province_country`(`country`) USING BTREE,
  CONSTRAINT `fk_province_country` FOREIGN KEY (`country`) REFERENCES `country` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB ROW_FORMAT = Dynamic;

CREATE TABLE `school_type`  (
  `id` int(0) NOT NULL,
  `name` varchar(255) NOT NULL,
  `country` int(0) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `fk_schooltype_country`(`country`) USING BTREE,
  CONSTRAINT `fk_schooltype_country` FOREIGN KEY (`country`) REFERENCES `country` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB ROW_FORMAT = Dynamic;

我的问题是，现在班级可以同时拥有一个省（拥有 A 国）和一个可以拥有 B 国的学校类型。这应该是不可能的。我可以改变什么？先感谢您！

标签： mysqlsqlforeign-keysrelationshipdata-modeling