SQL Query Throws Error 150

Question

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';

CREATE SCHEMA IF NOT EXISTS `rsmad` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `rsmad` ;

-- -----------------------------------------------------
-- Table `rsmad`.`app_flashobjects`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `rsmad`.`app_flashobjects` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `app_id` INT UNSIGNED NOT NULL ,
  `flashobject_id` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `rsmad`.`app_html`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `rsmad`.`app_html` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `app_id` INT UNSIGNED NOT NULL ,
  `html_id` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `rsmad`.`app_iframes`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `rsmad`.`app_iframes` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `app_id` INT UNSIGNED NOT NULL ,
  `iframe_id` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `rsmad`.`app_images`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `rsmad`.`app_images` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `app_id` INT UNSIGNED NOT NULL ,
  `image_id` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `rsmad`.`app_links`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `rsmad`.`app_links` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `app_id` INT UNSIGNED NOT NULL ,
  `link_id` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `rsmad`.`app_text`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `rsmad`.`app_text` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `app_id` INT UNSIGNED NOT NULL ,
  `text_id` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `rsmad`.`apps`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `rsmad`.`apps` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `webpage_id` INT UNSIGNED NOT NULL ,
  `timestamp` DATETIME NULL ,
  `status` ENUM('ok','malicious','good') NOT NULL DEFAULT 'ok' ,
  PRIMARY KEY (`id`) ,
  CONSTRAINT `fk_apps_app_flashobjects1`
    FOREIGN KEY (`id` )
    REFERENCES `rsmad`.`app_flashobjects` (`app_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_apps_app_html1`
    FOREIGN KEY (`id` )
    REFERENCES `rsmad`.`app_html` (`app_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_apps_app_iframes1`
    FOREIGN KEY (`id` )
    REFERENCES `rsmad`.`app_iframes` (`app_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_apps_app_images1`
    FOREIGN KEY (`id` )
    REFERENCES `rsmad`.`app_images` (`app_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_apps_app_links1`
    FOREIGN KEY (`id` )
    REFERENCES `rsmad`.`app_links` (`app_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_apps_app_text1`
    FOREIGN KEY (`id` )
    REFERENCES `rsmad`.`app_text` (`app_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

Can anyone explain what the issue with these queries is?

score 1 · Accepted Answer · edited May 23 '17 at 12:12

You are using polymorphic associations, which unfortunately can't be done with constraints because foreign keys can't share referencing columns. To get this working, give each foreign table reference their own column in apps:

CREATE  TABLE IF NOT EXISTS `rsmad`.`apps` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `webpage_id` INT UNSIGNED NOT NULL ,
  `timestamp` DATETIME NULL ,
  `status` ENUM('ok','malicious','good') NOT NULL DEFAULT 'ok' ,
  `flashobjects_id` INT UNSIGNED NOT NULL,
  `html_id` INT UNSIGNED NOT NULL,
  -- etc
  PRIMARY KEY (`id`) ,
  CONSTRAINT `fk_apps_app_flashobjects1`
    FOREIGN KEY (`flashobjects_id` )
    REFERENCES `rsmad`.`app_flashobjects` (`app_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_apps_app_html1`
    FOREIGN KEY (`html_id` )
    REFERENCES `rsmad`.`app_html` (`app_id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  -- etc

Edit: Actually, I think you should define the foreign key relationship the other way around. Foreign keys are defined in the child table and apps looks very much like a parent table to me. Now if you delete a row in any of the app_% tables, the entry in apps which they are related to will be cascade deleted.

SQL Query Throws Error 150

1 Answers1