Adding a user/accounts table to Postgres in Django View

Question

Go to edit 2

Calling the following adduser function in views.py. I save the user first because it's id (automatically created by Django upon INSERT) is the primary/foreign key for accounts and passwords. Adding a user seems to be working fine, but then when it gets to the Accounts(user=u), the following error throws:

IntegrityError at /adduser
insert or update on table "OmniCloud_App_accounts" violates foreign key constraint "user_id_refs_id_468fbcec324e93d2"
DETAIL:  Key (user_id)=(4) is not present in table "OmniCloud_App_user".

But the key should be there since it just saved the user to the db...

def adduser(request):
    username = request.POST['username']
    password = request.POST['password']
    u = User.objects.create_user(username, request.POST['email'], password)
    u.save()
    a = Accounts(user=u)
    p = Passwords(user=u)
    a.save()
    p.save()
    user = authenticate(username=username, password=password)
    if user is not None and user.is_active:
        auth.login(request, user)
        return HttpResponseRedirect("/%s/" %u.id)
    else:
        return HttpResponseRedirect("/account/invalid/")

EDIT: Here is the beginning to the initialization of Accounts:

from django.db import models
from django.contrib.auth.models import User
class Accounts(models.Model):   
    user = models.ForeignKey(User)

EDIT 2 I've realized, by going to the admin page, that when It complains that 4 isn't in the database, that is because it is adding the 3rd user. Adding the 4th user throws an error that there is no user with id 5 (duh). I don't see anything in the code that would cause it to search for user_id+1, any ideas

To the war chest!

Answer 1

My money is on a misspelled name. I notice in the error message that you have

OmniCloud_App_accounts
OmniCloud_App_user

Second table uses singular. There isn't a second table like this by chance:

OmniCloud_App_users

Also, using mixed case identifiers in PostgreSQL is a great source of reputation here on SO. It will bite you sooner or later. Victims of that folly are regulars here. Any table with this name maybe - and you forgot the double quotes in "OmniCloud_App_user" somewhere?

omnicloud_app_user

It's either that or the transaction saving the user has not been committed yet. Can you only create the user (and no accounts yet) and check if it ends up in the right database in the table and with the right ID?

Edit: tools to diagnose the problem

If you know that users are being created, then the question is: does the foreign key constraint user_id_refs_id_468fbcec324e93d2 look at the right place? Same database? Same schema? Same table?

To find out which tables exists in your database, try the following query (if you have the necessary privileges):

SELECT n.nspname AS schema_name
      ,c.relname AS table_name
      ,c.relhastriggers
      ,c.reltuples
FROM   pg_catalog.pg_class c
LEFT   JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE  c.relname ~~* '%user%'
AND    c.relkind = 'r'
AND    nspname <> 'pg_catalog';

Shows all tables in all schemas that have "user" in the name, case insensitive. Plus if the table has triggers (could cause your problem) and how many rows are in it (estimate updated by ANALYZE). Might give you a lead ...

You can also use the meta-command \d of the standard command line client (interactive terminal) psql.

I would run a test case and have the postgres server log everything it gets. Set this parameter for that purpose:

set log_statement = 'all';

The manual about logging-parameters.

log_statement (enum)

Controls which SQL statements are logged. Valid values are none (off), ddl, mod, and all (all statements).

The manual on how to set parameters.