Relationship problem in the Mother Table (Inheritance) in Postgresql

Question

I got the Mother Pay Table:

CREATE TABLE pagamento
(
  pagcod serial not null,      
  CONSTRAINT pk_pag PRIMARY KEY (pagcod)
)

And the two daughter tables Cash and Credit Card:

CREATE TABLE dinheiro
(
  desconto numeric(10,2) DEFAULT 0.00,
  juros numeric(10,2) DEFAULT 0.00
)inherits(pagamento); 

CREATE TABLE cartao_credito
(
  num_cartao integer NOT NULL,
  datavalidade date NOT NULL,
  senha character varying(256) NOT NULL,
  agencia integer NOT NULL,
  titular character varying(200) NOT NULL
)inherits(pagamento);

When I will give the INSERT in the MONEY table for example the PAGCOD attribute of the PAY mother table comes automatically:

INSERT INTO dinheiro(pagcod, desconto, juros)VALUES (4, 100.00, 12.5);

Giving a SELECT * FROM DINHERIO PK appears normally:

Just as giving a SELECT * FROM PAYMENT PK also appears normally:

PROBLEM: When I will try to reference this payment pk in the ORDER table:

CREATE TABLE pedido
(
  pedcod serial not null,
  peddata date NOT NULL ,
  ped_num_notafiscal integer NOT NULL,
  pedstatus varchar(20) NOT NULL DEFAULT 'ABERTO',
  ped_funcinario integer NOT NULL,
  ped_cliete integer NOT NULL,
  ped_pagamento integer,
  CONSTRAINT pk_pedido PRIMARY KEY (pedcod),
  CONSTRAINT fk_cliente FOREIGN KEY (ped_cliete)REFERENCES cliente (clicod),
  CONSTRAINT fk_funcionario FOREIGN KEY (ped_funcinario)REFERENCES funcionario (funcod),
  CONSTRAINT fk_pagamento FOREIGN KEY (ped_pagamento)REFERENCES pagamento (pagcod)  
);

The following error appears:

Could someone give me some light on how to solve this problem? The PK appears in the PAYMENT table, but when I will try to reference it in the REQUEST table give this error. The request’s INSERT syntax:

INSERT INTO public.pedido(pedcod, peddata, ped_num_notafiscal, pedstatus, ped_funcinario, ped_cliete, ped_pagamento)
    VALUES (7, '02-11-2017', 2323, 1, 1, 1, 4);

Answer 1

Reading the documentation: https://www.postgresql.org/docs/current/static/ddl-inherit.html#DDL-INHERIT-CAVEATS

I found that:

Other types of constraints (single key, primary key and foreign key restrictions) are not inherited.

and

A serious limitation of the inheritance resource is that indices (including exclusive restrictions) and external key restrictions apply only to individual tables, and not to their inheritances. This is true both on the referenced and referenced sides of a foreign key constraint.

That is, this behavior is expected by postgresql, you can not use inheritance between tables next to foreign keys.

You can remove the CONSTRAINT fk_pagamento and maybe do this check by Trigger.

I reproduced the situation in Sqlfiddle:

With the CONSTRAINT fk_pagamento (ERROR): http://sqlfiddle.com/#! 15/cd38b/2

Without the CONSTRAINT fk_pagamento: http://sqlfiddle.com/#! 15/491475/2

Answer 2

The restriction only searches the records directly linked to a pay table, something similar to FROM ONLY, and how the records were included in the descending tables (cash and credit card), there are then no records exclusively added to your table referenced in the foreign key. Integrity that searches the inherited tables that have accepted integrity between relationships has not yet been implemented.