Category Archives: django

Change Primary Key in PostgreSQL Tables via Django API (used as Foreign Key by other tables)

MTurk assignment ID is not always unique. When a worker return a HIT, the next worker taking it will have the same assignment ID.

Such being the case, when designing data structures for our own system to deploy tasks on MTurk iframe, it’s important not to use assignment ID as primary key for any table. However, if you already did this, here’s how I fixed it in Django (using PostgreSQL):

  1. You have an old table (defined in model.py), where you specify the assignment ID column to be primary key.
    Create new model object with exactly the same columns, except that: the model name is different (of course!) and the primary_key=True of your assignment ID column is removed

    Example
    	class Task(models.Model):
    	# other fields
    	assignment_id = models.CharField(max_length=30, primary_key = True)
    	class NewTable(models.Model):
    	# other fields
    	assignment_id = models.CharField(max_length=30)
    
    	class Result(models.Model):
    	# other fields
    	task = models.ForeignKey(Task)
    	# add the following line to all tables that refers your task object as foreign key
    	newtable = models.ForeignKey(NewTable, null=True)
  2. run in command line:
     python manage.py makemigrations
    
    	 python manage.py migrate
  3. Now you will have a empty new table with the correct schema constraints, the next thing to do is to copy existing contents to the new table.
      • You can choose to put your scripts in [yourappname]/management/commans/ folder, then run python manage.py [your filename without .py] to execute your code
      • Or you can make your changes directly in command line. Following is an example
    python manage.py shell
    from [yourappanme].models import *
    		for t in Task.objects.all():
    		 NewTable.objects.create(assignment_id=t.assignment_id, ...[other fields])
    		for t in Result.objects.all():
    		 nt = NewTable.objects.get(assignment_id=t.taskmeta.assignment_id)
    		 t.newtable = nt
    		 t.save()
  4. Now the new table is also a foreign key of those tables, you can remove the old task objects in your models.py, and migrate the changes
  5. Change the model name and field names (as foreign key) of NewTable in your models.py, then migrate the changes
  6. Done!