G
G
GeraldIstar2013-12-25 08:03:02
PostgreSQL
GeraldIstar, 2013-12-25 08:03:02

PostgreSQL doesn't see record with required key

There are three tables:

CREATE TABLE objects
(
  id bigint NOT NULL DEFAULT nextval('objects_seq'::regclass),
  create_date timestamp with time zone NOT NULL DEFAULT now(), -- Дата создания объекта
  edit_date timestamp with time zone NOT NULL DEFAULT now(), -- Дата последнего изменения объекта
  public boolean NOT NULL DEFAULT false, -- Является ли объект публичным (общедоступным)
  is_delete boolean NOT NULL DEFAULT false, -- Флаг того, что объект удален
  CONSTRAINT objects_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=TRUE
);

CREATE TABLE users
(
-- Унаследована from table objects:  id bigint NOT NULL DEFAULT nextval('objects_seq'::regclass),
-- Унаследована from table objects:  create_date timestamp with time zone NOT NULL DEFAULT now(),
-- Унаследована from table objects:  edit_date timestamp with time zone NOT NULL DEFAULT now(),
-- Унаследована from table objects:  public boolean NOT NULL DEFAULT false,
-- Унаследована from table objects:  is_delete boolean NOT NULL DEFAULT false,
  login character varying NOT NULL, -- Логин пользователя
  password character varying NOT NULL, -- Пароль
  expiration_date timestamp with time zone, -- Дата истечения
  first_name character varying NOT NULL,
  last_name character varying NOT NULL,
  middle_name character varying,
  phones bigint[],
  emails character varying[],
  active boolean NOT NULL DEFAULT true,
  CONSTRAINT users_pkey PRIMARY KEY (id),
  CONSTRAINT users_login_key UNIQUE (login)
)
INHERITS (objects)
WITH (
  OIDS=TRUE
);

CREATE TABLE favorite_objects
(
  id bigint NOT NULL DEFAULT nextval('favorite_objects_seq'::regclass),
  user_id bigint NOT NULL,
  favorite_object_id bigint NOT NULL,
  CONSTRAINT favorite_objects_pkey PRIMARY KEY (id),
  CONSTRAINT favorite_objects_favorite_object_id_fkey FOREIGN KEY (favorite_object_id)
      REFERENCES objects (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT favorite_objects_user_id_fkey FOREIGN KEY (user_id)
      REFERENCES users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=TRUE
);

When trying to add an entry to favorite_objects, it says that there is no entry with this id in the objects table. Although he is there. What is the problem - I will not put my mind to it.

Answer the question

In order to leave comments, you need to log in

1 answer(s)
G
GeraldIstar, 2013-12-25
@GeraldIstar

Problem solved. There was already a similar question, I just thought at first that it was not my case.
The problem is described in the manual, in section 5.8.1. Caveats
www.postgresql.org/docs/9.3/static/ddl-inherit.html
The bottom line is that when you add a record to a child table, you cannot refer to that record in the parent table.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question