Foreign keys in Postgres

Question

i´ve got a problem with foreign keys, it´s an strange problem.

First table:

CREATE TABLE adjunto
(
  id serial NOT NULL,
  codigo text,
  descripcion text,
  usuario integer,
  file integer,
  nombre text,
  propiedades hstore,

  CONSTRAINT adjunto_pkey PRIMARY KEY (id ),
  CONSTRAINT adjunto_file_fkey FOREIGN KEY (file)
      REFERENCES file (file_id) MATCH SIMPLE 
      ON UPDATE NO ACTION ON DELETE CASCADE
) WITH (
  OIDS=FALSE
);

Second table:

CREATE TABLE adjunto_coleccion_privada
(
  id serial NOT NULL,
  adjunto integer,
  coleccion integer,
  CONSTRAINT adjunto_coleccion_privada_pkey PRIMARY KEY (id ),
  CONSTRAINT adjunto_coleccion_privada_adjunto_fkey FOREIGN KEY (adjunto)
  REFERENCES adjunto (id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT adjunto_coleccion_privada_coleccion_fkey FOREIGN KEY (coleccion)
  REFERENCES coleccion (id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);

Command:

INSERT INTO adjunto_coleccion_privada (adjunto, coleccion) 
VALUES (600, 2) RETURNING id

Values 600 and 2 exist in both tables, adjunto and colecion.

Detailed error:

Mensaje: ERROR: insert or update on table "adjunto_coleccion_privada" 
                violates foreign key   
                constraint "adjunto_coleccion_privada_adjunto_fkey"
Detail: Key (adjunto)=(600) is not present in table "adjunto".

Did you double check that 600 is in adjunto ? What is the content of those 2 tables ? — Luc M
– Luc M, Commented Jul 17, 2012 at 20:38
I'm pretty sure Postgres is not lying. You don't have a row in adjunto with that ID — user330315
– user330315, Commented Jul 17, 2012 at 21:44

TheHuge_ · Accepted Answer · 2012-07-17 20:53:26Z

1

I tested your code (I dropped the adjunto_coleccion_privada_coleccion_fkey constraint since referred table does not exist in your pasted code).

I see no problem at all.

Are you really sure that there is a record with id = 600 in the adjunto table?

answered Jul 17, 2012 at 20:53

TheHuge_

1,03917 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Foreign keys in Postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related