Recursive Table nullable parent_id

Question

CREATE TABLE IF NOT EXISTS `db_teamup`.`programming_languages` (
      `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '',
      `name` VARCHAR(255) NOT NULL COMMENT '',
      `count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '',
      `parent_id` INT UNSIGNED NULL COMMENT '',
      `icon_path` VARCHAR(255) NOT NULL DEFAULT 'default_icon.svg' COMMENT '',
      `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`, `parent_id`)  COMMENT '',
INDEX `fk_programming_languages_programming_language_parent_idx`        (`parent_id` ASC)  COMMENT '',
CONSTRAINT `fk_programming_languages_programming_language_parent_id`
FOREIGN KEY (`parent_id`)
REFERENCES `db_teamup`.`programming_languages` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

I am trying to make a recursive mysql table, but when i execute the script, on workbench, it sets the parent_id to not null, is there a setting or a command that i am not executing before running my script? The worst part is that it makes the default value 0.

thank you

score 1 · Accepted Answer · answered Mar 07 '16 at 01:38

1

PRIMARY KEY ('id')

I don't think 'parent_id' should be part of your primary key. It wouldn't be able to be NULL if it was.

answered Mar 07 '16 at 01:38

kezsto

36
4

Recursive Table nullable parent_id

1 Answers1