Refactor foreign key to fields
Your seem to go the wrong way. Your original, normalized schema is typically superior. If you need to display shop / user, create a VIEW
.
But you may have your reasons, so here goes:
UPDATE purchases p
SET (shop, shop_user) = (s.name, s."user")
FROM shop s
WHERE s.id = p.shop_id;
Don't use the reserved word "user"
as identifier.
And "name" is hardly ever a good name, either.
And varchar(255)
in Postgres typically indicates a misunderstanding.
About varchar(255)
:
varchar(255)
- Should I add an arbitrary length limit to VARCHAR columns?
- Any downsides of using data type "text" for storing strings?
- More details in the manual.
Database foreign key in either one or another column
The problem was gone because at least we redefined our datamodel anyway. Thanks for your help anyway!
Using South to convert ForeignKey TO ManyToManyField not working out
In fact you are removing the field. Foreignkeys are represented by a column in your database that in that case would be named category_id. ManyToMany relationships are represented by a "through" table. With django you can either specif the through table or have one generated for you automatically.
This is a very nontrivial migration and you will need to hand code it. It will require a bit of understanding what the underlying database representation of your model is.
You will require 3 migrations to cleanly do this. First create a schemamigration with a dummy manytomany relationship to hold your data.
Then create a datamigration to copy the foreignkey relationships to your dummy manytomany
Finally create schemamigration to delete the foreignkey and rename the dummy manytomany table.
Steps 2 and 3 will both require you to manually write the migrations. I should emphasize this is a very nontrivial style of migration. However, it's entirely doable you just have to really understand what these relationships mean to the database more than your average migration. If you have little or no data it would be much simpler to just drop the tables and start over fresh with your migrations.
is it possible to override a foreign key relation with a custom method/property
You could retain but rename the foreign key and then add a property with the old name
class SatelliteModel(models.Model):
old_core = models.ForeignKey(CoreModel, null=True, blank=True, on_delete=models.SET_NULL)
@property
def core(self):
try:
return self.old_core
except CoreModel.DoesNotExist:
return aws_client.fetch_core()
This would change the column name in your schema, although you could override the column name to prevent this
old_core = models.ForeignKey(CoreModel, db_column='core_id', null=True, blank=True, on_delete=models.SET_NULL)
It may be possible to create a subclass of ForeignKey
that would perform as you wished, if this answer is not sufficient I can share some thoughts
Change type of Django model field from CharField to ForeignKey
This is likely a case where you want to do a multi-stage migration. My recommendation for this would look something like the following.
First off, let's assume this is your initial model, inside an application called discography
:
from django.db import models
class Album(models.Model):
name = models.CharField(max_length=255)
artist = models.CharField(max_length=255)
Now, you realize that you want to use a ForeignKey for the artist instead. Well, as mentioned, this is not just a simple process for this. It has to be done in several steps.
Step 1, add a new field for the ForeignKey, making sure to mark it as null:
from django.db import models
class Album(models.Model):
name = models.CharField(max_length=255)
artist = models.CharField(max_length=255)
artist_link = models.ForeignKey('Artist', null=True)
class Artist(models.Model):
name = models.CharField(max_length=255)
...and create a migration for this change.
./manage.py makemigrations discography
Step 2, populate your new field. In order to do this, you have to create an empty migration.
./manage.py makemigrations --empty --name transfer_artists discography
Once you have this empty migration, you want to add a single RunPython
operation to it in order to link your records. In this case, it could look something like this:
def link_artists(apps, schema_editor):
Album = apps.get_model('discography', 'Album')
Artist = apps.get_model('discography', 'Artist')
for album in Album.objects.all():
artist, created = Artist.objects.get_or_create(name=album.artist)
album.artist_link = artist
album.save()
Now that your data is transferred to the new field, you could actually be done and leave everything as is, using the new field for everything. Or, if you want to do a bit of cleanup, you want to create two more migrations.
For your first migration, you will want to delete your original field, artist
. For your second migration, rename the new field artist_link
to artist
.
This is done in multiple steps to ensure that Django recognizes the operations properly. You could create a migration manually to handle this, but I will leave that to you to figure out.
Renaming columns with foreign key constraints in mysql - anyway to make it easier?
You can rename the fields with dependency check with a dbForge Studio for MySQL (get Cristmas discounts - 20% off, or try Express version ;-). I cannot say the it will be faster, but it will help you to rename these fields and recreate keys automatically in a few steps - just change its name in a Database Explorer and click on Refactor button in message box.
Refactor SQLite Table by splitting it in two and link with foreign keys
Simple solution without triggers:
- create VEHICLES_TEMP table including the CAR_ID
- create your new CARS table without the VEHICLES columns you don't want
- update CARS with VEHICLE_ID taken from VEHICLES_TEMP (identified by the CAR_ID)
- create final VEHICLES table without the CAR_ID
How to rename a foreignkey field with South?
Update: with mysql-5.5.30-1.fc18.x86_64
and
MySQL-python==1.2.4
Django==1.4.2
South==0.7.6
the following works:
class Migration(SchemaMigration_:
def forwards(self, orm):
db.rename_column('app_model', 'old_id', 'new_id')
db.alter_column('app_model', 'new_id',
self.gf('django.db.models.fields.related.ForeignKey')(
blank=True,
null=True,
to=orm['app.OtherModel']
))
def backwards(self, orm):
db.rename_column('app_model', 'new_id', 'old_id')
db.alter_column('app_model', 'old_id',
self.gf('django.db.models.fields.related.ForeignKey')(
blank=True,
null=True,
to=orm['app.OtherModel']
))
As @Eloff comments, South can't find the original FK for reasons unknown, but it doesn't seem to matter. There is no need for a data migration (I believe) as pk values should not change.
The field specification (using self.gf
) is taken from South's auto-generated migrations for consistency.
Related Topics
Foreign Key Column Mapped to Multiple Primary Keys
MySQL Question About Scheduling
What SQL Databases Support Subqueries in Check Constraints
Select Second Most Minimum Value in Oracle
Setting Up Foreign Key with Different Datatype
Window Functions: Partition by One Column After Order by Another
How to Get Count() and Rows from One SQL Query in SQL Server
How to to Read a Xml from a Url Using T-Sql
Default Getdate for Insert Date
SQL Server Rounding Error, Giving Different Values
Bind a Column Default Value to a Function in SQL 2005
Rounding a Datetime Value Down to the Nearest Half Hour
When Shouldn't You Use a Relational Database
How to Create a "Unique" Constraint on a Boolean MySQL Column