1320920 - Foreign key constraint missing from job_exclusion in production

Status: RESOLVED FIXED

(Tree Management :: Treeherder, defect, P2)

Description

[Ed Morley [:emorley]]

The foreign key constraint on`author_id` is missing from production.

--

Django model:

class JobExclusion(models.Model):

    """
    A filter represents a collection of properties
    that you want to filter jobs on. These properties along with their values
    are kept in the info field in json format
    """
    name = models.CharField(max_length=255, unique=True)
    description = models.TextField(blank=True)
    info = JSONField()
    author = models.ForeignKey(User)

    ...

    class Meta:
        db_table = 'job_exclusion'


--- vagrant.sql	2016-11-25 17:22:53.439965200 +0000
+++ prod.sql	2016-11-25 17:31:49.775087600 +0000
...
 --
 -- Table structure for table `job_exclusion`
 --
 
 DROP TABLE IF EXISTS `job_exclusion`;
 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `job_exclusion` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `name` varchar(255) COLLATE utf8_bin NOT NULL,
   `description` longtext COLLATE utf8_bin NOT NULL,
   `info` longtext COLLATE utf8_bin NOT NULL,
   `author_id` int(11) NOT NULL,
   PRIMARY KEY (`id`),
   UNIQUE KEY `<INDEX_NAME>` (`name`),
-  KEY `<INDEX_NAME>` (`author_id`),
-  CONSTRAINT `<INDEX_NAME>` FOREIGN KEY (`author_id`) REFERENCES `auth_user` (`id`)
+  KEY `<INDEX_NAME>` (`name`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 /*!40101 SET character_set_client = @saved_cs_client */;

Comment 1

[Ed Morley [:emorley]]

The unedited create table statement from Vagrant is:

CREATE TABLE `job_exclusion` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_bin NOT NULL,
  `description` longtext COLLATE utf8_bin NOT NULL,
  `info` longtext COLLATE utf8_bin NOT NULL,
  `author_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `job_exclusion_author_id_9a3f9162_uniq` (`author_id`),
  CONSTRAINT `job_exclusion_author_id_9a3f9162_fk_auth_user_id` FOREIGN KEY (`author_id`) REFERENCES `auth_user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=189 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Fixed on dev/stage/prod using:

CREATE INDEX `job_exclusion_author_id_9a3f9162_uniq` ON `job_exclusion` (`author_id`);
ALTER TABLE `job_exclusion` ADD CONSTRAINT `job_exclusion_author_id_9a3f9162_fk_auth_user_id` FOREIGN KEY (`author_id`) REFERENCES `auth_user` (`id`);