Problem with the relationship in the Motherboard (Inheritance) in PostgreSql

0

I have the Mother Table Payment:

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

And the two daughters tables Money 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 am going to give the INSERT in the MONEY table for example the PAGCOD attribute of the PAYMENT mother table comes automatically:

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

Giving a SELECT * FROM DINNER The PK normally appears:

JustlikegivingaSELECT*FROMPAYMENTthePKalsoappearsnormally:

PROBLEM:WhenItrytoreferencethispaymentpkintheORDERtable:

CREATETABLEpedido(pedcodserialnotnull,peddatadateNOTNULL,ped_num_notafiscalintegerNOTNULL,pedstatusvarchar(20)NOTNULLDEFAULT'ABERTO',ped_funcinariointegerNOTNULL,ped_clieteintegerNOTNULL,ped_pagamentointeger,CONSTRAINTpk_pedidoPRIMARYKEY(pedcod),CONSTRAINTfk_clienteFOREIGNKEY(ped_cliete)REFERENCEScliente(clicod),CONSTRAINTfk_funcionarioFOREIGNKEY(ped_funcinario)REFERENCESfuncionario(funcod),CONSTRAINTfk_pagamentoFOREIGNKEY(ped_pagamento)REFERENCESpagamento(pagcod));

Thefollowingerrorappears:

Could someone give me a light on how to solve this problem? The PK appears in the PAY table, but when I try to reference it in the REQUEST table give this error. The ORDER 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);
    
asked by anonymous 02.11.2017 / 06:32

1 answer

0

Reading the documentation: link

I found that:

  

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

and

  

A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints apply only to individual tables, not their inheritances. This is true on both 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 CONSTRAINT fk_pagamento and perhaps perform this trigger check.

I reproduce the situation in SQLFiddle:

With CONSTRAINT fk_pagamento (ERROR): link

Without CONSTRAINT fk_pagamento : link

    
02.11.2017 / 15:00