[eluser]ibnclaudius[/eluser]
Hello,
I have three users tables:
Code:
-- -----------------------------------------------------
-- Table `new_schema1`.`students`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `new_schema1`.`students` (
`id` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(45) NOT NULL ,
`email` VARCHAR(45) NOT NULL ,
`password` VARCHAR(45) NOT NULL ,
`role` INT NOT NULL DEFAULT 1 ,
`school_id` INT NOT NULL ,
PRIMARY KEY (`id`) ,
UNIQUE INDEX `email_UNIQUE` (`email` ASC) ,
INDEX `fk_students_schools1` (`school_id` ASC) ,
CONSTRAINT `fk_students_schools1`
FOREIGN KEY (`school_id` )
REFERENCES `new_schema1`.`schools` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `new_schema1`.`teachers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `new_schema1`.`teachers` (
`id` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(45) NOT NULL ,
`email` VARCHAR(45) NOT NULL ,
`password` VARCHAR(45) NOT NULL ,
`role` INT NOT NULL DEFAULT 2 ,
`school_id` INT NOT NULL ,
PRIMARY KEY (`id`) ,
UNIQUE INDEX `email_UNIQUE` (`email` ASC) ,
INDEX `fk_teachers_schools1` (`school_id` ASC) ,
CONSTRAINT `fk_teachers_schools1`
FOREIGN KEY (`school_id` )
REFERENCES `new_schema1`.`schools` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `new_schema1`.`school_admins`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `new_schema1`.`school_admins` (
`id` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(45) NOT NULL ,
`email` VARCHAR(45) NOT NULL ,
`password` VARCHAR(45) NOT NULL ,
`role` INT NOT NULL ,
`school_id` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`id`) ,
UNIQUE INDEX `email_UNIQUE` (`email` ASC) ,
CONSTRAINT `fk_school_admins_schools1`
FOREIGN KEY (`school_id` )
REFERENCES `new_schema1`.`schools` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
Is it possible to auto-increment according to the three tables?
For example, I add a student, so it will have ID 1, then I add a teacher, so it will have ID 2, then...
I know I can make just one table for the three types of users, but in my case will be better to separate.
Thanks,
Claudius Ibn
