MTurk assignment ID is not always unique. When a worker return a HIT, the next worker taking it will have the same assignment ID.<\/p>\n
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):<\/p>\n
Example\r\n\tclass Task(models.Model):\r\n\t# other fields\r\n\tassignment_id = models.CharField(max_length=30, primary_key = True)\r\n\tclass NewTable(models.Model):\r\n\t# other fields\r\n\tassignment_id = models.CharField(max_length=30)\r\n\r\n\tclass Result(models.Model):\r\n\t# other fields\r\n\ttask = models.ForeignKey(Task)\r\n\t# add the following line to all tables that refers your task object as foreign key\r\n\tnewtable = models.ForeignKey(NewTable, null=True)<\/strong><\/pre>\n<\/li>\n- run in command line:\n
python manage.py makemigrations\r\n\r\n\t python manage.py migrate<\/pre>\n<\/li>\n- 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.\n
\n
- \n
\n
- 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<\/li>\n
- Or you can make your changes directly in command line. Following is an example<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n
python manage.py shell<\/pre>\nfrom [yourappanme].models import *\r\n\t\tfor t in Task.objects.all():\r\n\t\t NewTable.objects.create(assignment_id=t.assignment_id, ...[other fields])\r\n\t\tfor t in Result.objects.all():\r\n\t\t nt = NewTable.objects.get(assignment_id=t.taskmeta.assignment_id)\r\n\t\t t.newtable = nt\r\n\t\t t.save()<\/pre>\n<\/li>\n- Now the new table is also a foreign key of those tables, you can remove the old task objects in your\u00a0models.py<\/em>, and migrate the changes<\/li>\n
- Change the model name and field names (as foreign key) of NewTable in your models.py, <\/em>then migrate the changes<\/li>\n
- Done!<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"
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. … Continue reading Change Primary Key in PostgreSQL Tables via Django API (used as Foreign Key by other tables)<\/span>