    :defaults features-defaults
    :columns features-columns})
 
+; Order is important to avoid creating duplicate indexes. A foreign
+; key constraint creates an index if one doesn't exist, so create our
+; index before adding the constraint.
 (def codes-table
   ["CREATE TABLE IF NOT EXISTS `codes` (
-  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
-  `features_id` int(11) NOT NULL,
-   FOREIGN KEY (`features_id`) REFERENCES `features` (`id`) ON DELETE CASCADE,
+   `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
+   `features_id` int(11) NOT NULL,
    `ordering` int(10) unsigned NOT NULL,
-   `value` varchar(255) NOT NULL)"
-   "CREATE INDEX IF NOT EXISTS `feature` ON `codes` (`features_id`, `ordering`)"])
+   `value` varchar(255) NOT NULL,
+   UNIQUE (`features_id`, `ordering`),
+   FOREIGN KEY (`features_id`) REFERENCES `features` (`id`) ON DELETE CASCADE)"])
 
 (defentity codes
   (belongs-to features))

  ["CREATE TABLE IF NOT EXISTS `probes` (
   `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   `eid` INT NOT NULL,
   FOREIGN KEY (eid) REFERENCES `experiments` (`id`),
   `name` VARCHAR(255))"
   "ALTER TABLE `probes` ADD CONSTRAINT IF NOT EXISTS `eid_name` UNIQUE(`eid`, `name`)"
   "CREATE INDEX IF NOT EXISTS probe_name ON probes (eid, name)"])

(def scores-table
  [(format "CREATE TABLE IF NOT EXISTS `scores` (

  ["CREATE TABLE IF NOT EXISTS `probemap_probes` (
   `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   `probemaps_id` INT NOT NULL,
   `probe` VARCHAR(1000) NOT NULL,
   FOREIGN KEY (probemaps_id) REFERENCES `probemaps` (`id`) ON DELETE CASCADE)"])

; XXX CASCADE might perform badly. Might need to do this incrementally in application code.
(def probemap-positions-table
  ["CREATE TABLE IF NOT EXISTS `probemap_positions` (
   `probemaps_id` INT NOT NULL,
   `probemap_probes_id` INT NOT NULL,
   `bin` INT,
   `chrom` VARCHAR(255) NOT NULL,
   `chromStart` INT NOT NULL,
   `chromEnd` INT NOT NULL,
   `strand` CHAR(1))"
   "CREATE INDEX IF NOT EXISTS chrom_bin ON probemap_positions (`probemaps_id`, `chrom`, `bin`)"
   "ALTER TABLE `probemap_positions` ADD FOREIGN KEY (`probemaps_id`) REFERENCES `probemaps` (`id`) ON DELETE CASCADE"
   "ALTER TABLE `probemap_positions` ADD FOREIGN KEY (`probemap_probes_id`) REFERENCES `probemap_probes` (`id`) ON DELETE CASCADE"])

(def probemap-genes-table
  ["CREATE TABLE IF NOT EXISTS `probemap_genes` (
   `probemaps_id` INT NOT NULL,
   `probemap_probes_id` INT NOT NULL,
   `gene` VARCHAR(255))"
   "CREATE INDEX IF NOT EXISTS probemap_gene ON `probemap_genes` (`probemaps_id`, `gene`)"
   "ALTER TABLE `probemap_genes` ADD FOREIGN KEY (`probemaps_id`) REFERENCES `probemaps` (`id`) ON DELETE CASCADE"
   "ALTER TABLE `probemap_genes` ADD FOREIGN KEY (`probemap_probes_id`) REFERENCES `probemap_probes` (`id`) ON DELETE CASCADE"])

;
; feature tables
