 -- -----------------------------------------------------
 CREATE TABLE IF NOT EXISTS `igfdb`.`collection_group` (
   `collection_group_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
-  `collection_id` INT UNSIGNED NULL,
-  `file_id` INT UNSIGNED NULL,
+  `collection_id` INT UNSIGNED NOT NULL,
+  `file_id` INT UNSIGNED NOT NULL,
   PRIMARY KEY (`collection_group_id`),
   CONSTRAINT `fk_collection_group_1`
     FOREIGN KEY (`collection_id`)
     REFERENCES `igfdb`.`collection` (`collection_id`)
-    ON DELETE SET NULL
+    ON DELETE CASCADE
     ON UPDATE CASCADE,
   CONSTRAINT `fk_collection_group_2`
     FOREIGN KEY (`file_id`)
     REFERENCES `igfdb`.`file` (`file_id`)
-    ON DELETE SET NULL
+    ON DELETE CASCADE
     ON UPDATE CASCADE)
 ENGINE = InnoDB CHARSET=UTF8;
 

CREATE TABLE IF NOT EXISTS `igfdb`.`sample` (
  `sample_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `igf_id` VARCHAR(10) NOT NULL,
  `taxon_id` INT NULL DEFAULT NULL,
  `scientific_name` VARCHAR(50) NULL DEFAULT NULL,
  `common_name` VARCHAR(50) NULL DEFAULT NULL,
  `donor_anonymized_id` VARCHAR(10) NULL DEFAULT NULL,
  `description` VARCHAR(50) NULL DEFAULT NULL,
  `phenotype` VARCHAR(45) NULL DEFAULT NULL,
  `sex` ENUM('FEMALE', 'MALE', 'OTHER', 'UNKNOWN') NOT NULL DEFAULT 'UNKNOWN',
  `status` ENUM('ACTIVE', 'FAILED', 'WITHDRAWN') NOT NULL DEFAULT 'ACTIVE',
  `biomaterial_type` ENUM('PRIMARY_TISSUE', 'PRIMARY_CELL', 'PRIMARY_CELL_CULTURE', 'CELL_LINE', 'UNKNOWN') NOT NULL DEFAULT 'UNKNOWN',
  `cell_type` VARCHAR(50) NULL,
  `tissue_type` VARCHAR(50) NULL DEFAULT NULL,
  `cell_line` VARCHAR(50) NULL,
  `project_id` INT UNSIGNED NULL,
  PRIMARY KEY (`sample_id`),
  UNIQUE INDEX `igf_id_UNIQUE` (`igf_id`),

  `sample_attribute_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `sample_attribute_name` VARCHAR(30) NULL,
  `sample_attribute_value` VARCHAR(50) NULL,
  `sample_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`sample_attribute_id`),
  UNIQUE INDEX `sample_attribute_name_UNIQUE` (`sample_attribute_name` ASC),
  UNIQUE INDEX `sample_attributecol_UNIQUE` (`sample_id` ASC),
  CONSTRAINT `fk_sample_attribute_1`
    FOREIGN KEY (`sample_id`)
    REFERENCES `igfdb`.`sample` (`sample_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB CHARSET=UTF8;

