0

Is it possible to define a field that auto-increments within the value of a foreign key?

E.g. if we define Company and Department models in Django:

class Company(models.Model):
    name = models.CharField(max_length=25)

class Department(models.Model):
    company = models.ForeignKey(Company)
    department_number = ...?   # should start from 1 for each company

in a pure SQL implementation the pair (company_id, department_number) would be a composite primary key.

I'm looking for solutions in Django or SQL that are safe (in terms of multiple processes creating departments simultaneously) and efficient. I'm using MySQL, but comparisons with other databases are also welcome.

thebjorn
  • 26,297
  • 11
  • 96
  • 138

1 Answers1

1

On SQL server one can use TRIGGER to do something extra after insert. Trigger is transaction safe and could be rolled back. Another way is to use an intermediate sequence table.

frost-nzcr4
  • 1,540
  • 11
  • 16