why postgreSQL can't find foreign key

Question

Why django reports a "missing foreign key error" when creating a record referencing a key just created ?

have a look a this code:

def doRegister(request,username, email, password):
    user = User.objects.create_user( username, email, password )
    realm = createWhereAvailable( user )
    realm.save()
    return redirect('index')

def createWhereAvailable( user ):
    #some logic here... note: Realm extends django's Model
    return Realm( x=x, y=y,  user=user, name=generateRandomRealmName(x, y) )

the doRegister view raises an exception at realm.save():

IntegrityError at /myapp/doregister
ERREUR:  une instruction insert ou update sur la table « myapp_realm » viole la contrainte de clé
étrangère « user_id_refs_id_9302d6bb »
DETAIL:  La clé (user_id)=(8) n'est pas présente dans la table « myapp_user ».

which can be translated "the key (user_id)=(8) can't be found in table myapp_user"

But the record in User with an id=8 exists

> > User.objects.get(id=8)
<User: tg>

note: also, the id=8 is being recycled here. It has been already given for a previous record that has then been deleted.

Edit

Even django administration can't add a Realm record refering this user. But the strange thing is that it's ok with old records (id=1) which haven't been recycled.

I suspect it might be related to recycling because some people are experiencing similar issues (duplicate keys): http://www.vlent.nl/weblog/2011/05/06/integrityerror-duplicate-key-value-violates-unique-constraint/ or IntegrityError duplicate key value violates unique constraint - django/postgres

I've put more about the table below

Here is the output of the manage.py sql command:

BEGIN;
CREATE TABLE "myapp_realm" (
    "id" serial NOT NULL PRIMARY KEY,
    "user_id" integer NOT NULL REFERENCES "auth_user" ("id") DEFERRABLE INITIALLY DEFERRED,
    "name" varchar(30) NOT NULL,
    #... some other fields stripped for consision
)
;

COMMIT;

and here's the serialisation of the table from pgadmin:

CREATE TABLE myapp_realm
(
  id serial NOT NULL,
  user_id integer NOT NULL,
  name character varying(30) NOT NULL,
  #... some other fields
  CONSTRAINT myapp_realm_pkey PRIMARY KEY (id),
  CONSTRAINT user_id_refs_id_9302d6bb FOREIGN KEY (user_id)
      REFERENCES myapp_user (id) MATCH Unknown
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED
)
WITH (
  OIDS=FALSE
);

Have you done anything affecting django's transaction scheme? Or is it the default? — rantanplan, Jul 24 '13 at 13:58
Also, when you do `User.objects.create_user()` the user gets created on that specific line, so the `user.save()` below that line is redundant. — rantanplan, Jul 24 '13 at 14:04
I'm a beginner with django and don't remeber having modified manually the transaction scheme. But I use South for migrations, If it changes something. As for the `user.save()`, I've just figured it out and shortened the code in my question. I added some edits because I realised django admin goes wrong too. — BiAiB, Jul 24 '13 at 14:15
Well unless you have done something exotic in postgresql's settings, I would look into any weird `south` side effects. If it is an option for you, I'd suggest creating a project environment without `south` and with a clean database. Then I'd do the same thing you do here, to see if the problem persists. — rantanplan, Jul 24 '13 at 14:27
I'll try to do that when I can. First, I'll try to see if I have the issue on my Linux setup — BiAiB, Jul 24 '13 at 15:46
well, the bug doesn't happen in my linux(psql (PostgreSQL) 9.1.9). When I create new Users, the database doesn't recycle ids. Also, the foreign key constraint is different in my second installation: `CONSTRAINT user_id_refs_id_756985c4a7cd2509 FOREIGN KEY (user_id) REFERENCES auth_user (id) MATCH SIMPLE`. it references the correct table. But the query in my post references an old table `myapp_user` I migrated from with south. — BiAiB, Jul 24 '13 at 20:16
It seems that postgresql's windows package comes with different default settings then. Either that or it's simply better to keep developing on Linux because it is more well tested there. Also I'd suggest steering clear of `south` while you're in development and prefer to drop/syncdb when you change your db schema. — rantanplan, Jul 24 '13 at 21:04
@DivinusVox It's a bit old now, but at the time I just reset the whole database, it was a test environnement. I never did any dump between my windows and linux versions. I was using syncdb and south migrations to update the database. — BiAiB, Nov 02 '13 at 14:58

why postgreSQL can't find foreign key

0 Answers0