On Sat, Sep 26, 2009 at 9:03 AM, Robb Bossley <robb.boss...@gmail.com>wrote:
> All,
>
> I am struggling to fix a database in such a way that I can make sure that
> each entry is unique within its framework.
>
> Suppose the following, in a library setting:
>
> (Using Django 1.0 and SQL Server)
>
> An author may have multiple books. The books may have the same price.
> They may even have the same publisher. But each library branch only owns
> one copy. (This is just an example case - so the analogy does not entirely
> work in some cases, but you get the idea.)
>
> So originally I created a table for with each book containing a separate
> id. However, I realized that the same book could get entered twice, as I am
> not checking for this scenario before I enter the newly received book. And
> I cannot have duplicate entries. So I came up with the brilliant idea to
> change the database to a composite key, consisting of the author, price,
> publisher and library. I accordingly went in and changed the models.py -
> something like this:
>
> class Book(models.Model):
> author_id = models.ForeignKey(Authors, db_column='Author_ID',
> primary_key=True, editable=False)
> price = models.DecimalField(decimal_places=2, db_column=u'Price')
> publisher = models.ForeignKey(Publishers, db_column='Publisher')
> library = models.ForeignKey(Libraries, db_column='Library')
>
> class Meta:
> unique_together = ('author_id', 'price', 'publisher', 'library')
>
> Here is what views.py has:
>
> def book_input(request, author_id):
> a = get_object_or_404(Authors, pk=author_id)
> if request.method == 'POST':
> form = BookInputForm(author_id, request.POST)
> if form.is_valid():
> newbook = form.save(commit=False)
> newbook.author_id = a
> newbook.save()
> return HttpResponseRedirect('/libraries/')
> else:
> form = BookInputForm(author_id)
> return render_to_response('Book_Input.html', {'author':a, 'form':form,
> })
>
> Before I changed to this composite primary key idea, i could potentially
> get a duplicate entry, but the entry did work. Now, when I try to enter any
> item, the SQL server gives me an error saying:
>
> "Violation of PRIMARY KEY constraint... Cannot insert duplicate key in
> object...
>
> Have I missed something, because I checked and the combination that I was
> trying to insert is indeed unique based upon the criteria I have set in the
> database?
>
>
You say an author may have multiple books, but by placing primary_key=True
on author_id you've made it so that each author_id can appear only once in
the Book table. So you seem to have added a constraint that prevents an
author from having multiple books.
Why did you put primary_key=True on the author_id field? I don't see how
needing that follows from anything you have said. You can specify whatever
set of fields you want to be unique as a set in unique_together to
accomplish what you need. None of them should be specified as the primary
key, because it is only together with the others that they need to be
unique.
Note this approach does not give you a composite primary key. Django does
not support these (maybe someday it will, but not at present). You'll need
another field (the automatic one you'll get if you don't specify primary_key
on any field should be fine) to be the primary key field.
Karen
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
