Ensure SQL supertype table has subtype

Question

I have the following database schema.

Ensuring that an entity record exists for each parent record is trivial due to the non-NULL FK constraint.

How can I ensure that a child record exists for each parent record?

ERD

-- MySQL Script generated by MySQL Workbench
-- 01/07/15 06:01:50
-- Model: New Model    Version: 1.0
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';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`entity`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`entity` (
  `identity` INT NOT NULL,
  `stuff` VARCHAR(45) NULL,
  PRIMARY KEY (`identity`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`parent1`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`parent1` (
  `entity_identity` INT NOT NULL,
  `stuff` VARCHAR(45) NULL,
  PRIMARY KEY (`entity_identity`),
  CONSTRAINT `fk_parent1_entity`
    FOREIGN KEY (`entity_identity`)
    REFERENCES `mydb`.`entity` (`identity`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`parent2`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`parent2` (
  `entity_identity` INT NOT NULL,
  `stuff` VARCHAR(45) NULL,
  PRIMARY KEY (`entity_identity`),
  CONSTRAINT `fk_parent2_entity1`
    FOREIGN KEY (`entity_identity`)
    REFERENCES `mydb`.`entity` (`identity`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`child1_1`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`child1_1` (
  `parent1_entity_identity` INT NOT NULL,
  `stuff` VARCHAR(45) NULL,
  PRIMARY KEY (`parent1_entity_identity`),
  CONSTRAINT `fk_child1_1_parent11`
    FOREIGN KEY (`parent1_entity_identity`)
    REFERENCES `mydb`.`parent1` (`entity_identity`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`child1_2`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`child1_2` (
  `parent1_entity_identity` INT NOT NULL,
  `stuff` VARCHAR(45) NULL,
  PRIMARY KEY (`parent1_entity_identity`),
  CONSTRAINT `fk_child1_2_parent11`
    FOREIGN KEY (`parent1_entity_identity`)
    REFERENCES `mydb`.`parent1` (`entity_identity`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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

score 2 · Answer 1 · answered Jan 08 '15 at 15:38

I think this must be enforced by the application.

Trying to enforce this with triggers creates a sequence problem. If you can't create a Parent without an Entity, and you can't create an Entity without a Parent, then which do you create first? At the time you create either record, there's no way for the database engine to "know" whether you are going to create the other record in the next few milliseconds or not.

In code, though, you could collect all the data, and then write both records if all the required data is available, or put out an error message if it's not.

Depending on your requirements, you COULD have a trigger that is fired on the creation of an Entity record that automatically creates the Parent record with default values, or vice versa. Whether such a dummy record would be useful, I can't say without knowing more about your app.

You could have a stored procedure that creates both Entity and Parent records, so if a program calls the sproc, it MUST create both. But I don't know any way, in any database engine that I'm familiar with, to say that the ONLY way that a program can create a record is through a particular stored procedure. So enforcing use of the procedure would rely on programmers following the convention. And if programmers follow the convention to use the stored procedure, they should be able to follow the convention to always create both records by whatever method they're using.

My thought would be to create a library function that creates the pair, and that returns some sort of error condition if you don't have all the data required to create both. Thoroughly debug this function. Then tell the programmers to always use this function to create the records. Then if there's a problem, there's one place to look.

Mohsen Heydari · Accepted Answer · 2015-01-07T16:01:24.100

1

Having a super type in you design will ensure adding more child tables will be possible, without need of your per exist tables and application being restructured. It means parent will be unaware of children.

If you are facing parent records with no child that may be an evidence of a defect in the application.
BTW a mechanism to enforce this check constraint will be using triggers.

As a hint (if using ORMs) Having parent entities being abstract classes will be an alternative.

edited Jan 07 '15 at 16:01

answered Jan 07 '15 at 15:55

Mohsen Heydari

7,256
4
31
46

Thanks Mohsen. Yes, it appears a defect in the application. Transactions appear to be a good way to enforce (http://stackoverflow.com/questions/27822385/enforce-inserting-records-into-multiple-tables). If I wanted to, I suppose I could use transactions in a stored procedure, but will probably not do so. – user1032531 Jan 07 '15 at 16:28

Ensure SQL supertype table has subtype

2 Answers2