Changing the value of the primary key
Torsten Bronger
When I change the value of a primary key field (assuming that it is
still unique), are depending tables also updated? I found on the
net that "ON UPDATE CASCADE" has to be set for the child tables, but
I didn't find anything Django-related.
Tschö,
Torsten.
--
Torsten Bronger, aquisgrana, europa vetus
Jabber ID: torsten...@jabber.rwth-aachen.de
Dan
Can I ask why you would need to change PKs?
Torsten Bronger
Dan writes:
> Django sets this for you if your database supports it (ie: you are
> using postgres or mysql with innodb tables). If you are using
> mysql, check that tables aren't MyISAM.
Okay, I have InnoDB.
> If you want to do this inside Django, more complex because if you
> change an object's primary key, Django concludes that you want to
> work with another row altogether and doesn't modify your original
> row.
So what happens when I say
my_db_object.its_primary_key = new_value
my_db_object.save()
? Is a new object created or the row changed? If the first, how do
I change the value at all?
> Can I ask why you would need to change PKs?
Yes. We are a scientific institution, and I create a samples
database. Frequently a sample gets a new name, however, the old
name(s) must be kept as aliases.
For the aliases, I probably will use a second table with many-to-one
relationship.
I wanted to keep the (current) sample name the primary key because I
think that this accelerates lookup, but if all else fails, I must
use a surrogate key.
Michael Glassford
> Hallöchen!
>
> Dan writes:
>
>> Django sets this for you if your database supports it (ie: you are
>> using postgres or mysql with innodb tables). >If you are using
>> mysql, check that tables aren't MyISAM.
Are you saying that Django adds the "ON UPDATE CASCADE" to the database
definition? I've never noticed it doing this (unless you're using a
patch that I submitted that does this for Foreign Keys where tell it to
do so).
Or, are you saying that Django emulates this behavior? I don't think
that's correct either (see the URL I reference below).
Maybe I misunderstood you?
> Okay, I have InnoDB.
>
>> If you want to do this inside Django, more complex because if you
>> change an object's primary key, Django concludes that you want to
>> work with another row altogether and doesn't modify your original
>> row.
>
> So what happens when I say
>
> my_db_object.its_primary_key = new_value
> my_db_object.save()
>
> ? Is a new object created or the row changed?
A new row is created--unless you change it to a value of an
already-existing row. See
http://www.djangoproject.com/documentation/db-api/#how-django-knows-to-update-vs-insert.
> If the first, how do
> I change the value at all?
>
>> Can I ask why you would need to change PKs?
>
> Yes. We are a scientific institution, and I create a samples
> database. Frequently a sample gets a new name, however, the old
> name(s) must be kept as aliases.
>
> For the aliases, I probably will use a second table with many-to-one
> relationship.
>
> I wanted to keep the (current) sample name the primary key because I
> think that this accelerates lookup, but if all else fails, I must
> use a surrogate key.
>
> Tschö,
> Torsten.
>
>
> On Fri, Jul 18, 2008 at 7:58 AM, Torsten Bronger <bro...@physik.rwth-aachen.de> wrote:
>
>
> Hallöchen!
>
> When I change the value of a primary key field (assuming that it is
> still unique), are depending tables also updated? I found on the
> net that "ON UPDATE CASCADE" has to be set for the child tables, but
> I didn't find anything Django-related.
>
Dan
Are you saying that Django adds the "ON UPDATE CASCADE" to the database
definition? I've never noticed it doing this (unless you're using a
patch that I submitted that does this for Foreign Keys where tell it to
do so).
Or, are you saying that Django emulates this behavior? I don't think
that's correct either (see the URL I reference below).
I was under the assumption it was doing this as it was creating foreign key constraints, I might be wrong.
> Yes. We are a scientific institution, and I create a samples
> database. Frequently a sample gets a new name, however, the old
> name(s) must be kept as aliases.
> For the aliases, I probably will use a second table with many-to-one
> relationship.
I don't think it's best practice to give meaningful names to primary key field in Django, you should probably have field name and let Django auto-create a id field. The relationship table is a good idea but if you want to alias different models, you could take a look at generic relationship and "tag" your samples with the old names.
J Meier
On Jul 18, 7:41 am, Torsten Bronger <bron...@physik.rwth-aachen.de>
wrote:
>
> Yes. We are a scientific institution, and I create a samples
> database. Frequently a sample gets a new name, however, the old
> name(s) must be kept as aliases.
>
> For the aliases, I probably will use a second table with many-to-one
> relationship.
>
> I wanted to keep the (current) sample name the primary key because I
> think that this accelerates lookup, but if all else fails, I must
> use a surrogate key.
immutable primary key for your data. I'd suggest something like a
composite key made of date/time, sample no., and fk to experiment
(whatever an experiment's pk is), but django doesn't support composite
keys. So a "surrogate" is a good compromise here. As for names, since
each sample can have multiple names, it should be a separate table
with a foreign key to the samples table.
class Experiment(models.Model):
date = ...
etc ....
class Sample(models.Model):
experiment = models.ForeignKey(Experiment)
date = models.DateTimeField()
sampleno = models.PositiveIntegerField()
... data ...
class Meta:
unique_together = (('experiment', 'date', 'sampleno'),)
class SampleName(models.Model):
sample = models.ForeignKey(Sample)
name = models.CharField(max_length = 60)
named = models.DateTimeField()
Primary keys never change over time.
-Jim
Torsten Bronger
Dan writes:
> [...]
>
> I don't think it's best practice to give meaningful names to
> primary key field in Django, you should probably have field name
> and let Django auto-create a id field.
You're right. I now also think that Django's abstraction layer for
the RDBMS -- albeit thin -- means that you're better off with
unique=True fields in all cases. The common advantages of primary
keys are mimicked by Django very well.
I only hope that InnoDB's lookup works for unique=True fields as
fast as for primary_key=True fields ... ;-)