db/00006/AlleleCascades.pm

   1 package AlleleCascades;
   2 use Moose;
   3 extends 'CXGN::Metadata::Dbpatch';
   4
   5 has '+description' => ( default => <<'' );
   6 Adding cascading delete to some unique constraints on stock_allele and allele tables.
   7
   8 has '+prereq' => (
   9     default => sub {
  10         ['StockSgnLinks'],
  11     },
  12   );
  13
  14 sub patch {
  15
  16     shift->dbh->do(<<EOSQL);
  17
  18 SET search_path=phenome,public;
  19
  20 ALTER TABLE stock_allele
  21   DROP CONSTRAINT stock_allele_allele_id_fkey,
  22   DROP CONSTRAINT stock_allele_stock_id_fkey;
  23
  24 ALTER TABLE stock_allele
  25   ADD CONSTRAINT stock_allele_allele_id_fkey
  26                  FOREIGN KEY (allele_id)
  27                  REFERENCES phenome.allele(allele_id)
  28                  ON DELETE CASCADE,
  29   ADD CONSTRAINT stock_allele_stock_id_fkey
  30                  FOREIGN KEY (stock_id)
  31                  REFERENCES stock(stock_id)
  32                  ON DELETE CASCADE;
  33
  34 ALTER TABLE allele
  35    DROP CONSTRAINT allele_locus_id_fkey;
  36
  37 ALTER TABLE allele
  38    ADD CONSTRAINT allele_locus_id_fkey
  39                   FOREIGN KEY (locus_id)
  40                   REFERENCES locus(locus_id)
  41                   ON DELETE CASCADE;
  42
  43 EOSQL
  44
  45     print "Done.";
  46     return 1;
  47 }
  48
  49 1;