Multiple primary key problem

Question

Multiple primary key problem

Navigation

#1 by (2 votes)

0

I have the table in which 2 fields are found as primary keys

CREATE TABLE public.productounidad
(
prod_cod character varying(30) NOT NULL,
uni_nombre character varying(100) NOT NULL,
pu_cantidad numeric(8,2) DEFAULT 0,
pu_minimo numeric(8,2) DEFAULT 0,
pu_maximo numeric(8,2) DEFAULT 0,
pu_costo numeric(8,2) DEFAULT 0,
pu_precionormal numeric(8,2) DEFAULT 0,
pu_precioespecial numeric(8,2) DEFAULT 0,
CONSTRAINT productounidad_pkey PRIMARY KEY (prod_cod, uni_nombre),
CONSTRAINT productounidad_prod_cod_fkey FOREIGN KEY (prod_cod)
  REFERENCES public.producto (prod_cod) MATCH SIMPLE
  ON UPDATE CASCADE ON DELETE NO ACTION,
CONSTRAINT productounidad_uni_nombre_fkey FOREIGN KEY (uni_nombre)
  REFERENCES public.unidad (uni_nombre) MATCH SIMPLE
  ON UPDATE CASCADE ON DELETE NO ACTION
)

and the reference in the following table:

CREATE TABLE public.detalle
(
det_cod SERIAL PRIMARY KEY,
det_cod_prod character varying(30),
det_nombre_uni character varying(100),
det_cod_doc integer,
det_fecha date NOT NULL,
det_descrip character varying(40),
det_cant integer,
det_ing_egr character(1),
det_prec_comp numeric(10,2) DEFAULT 0,
det_prec_comp_t numeric(10,2) DEFAULT 0,
det_prec_vent numeric(10,2) DEFAULT 0,
det_existencia integer,
det_costo_prom numeric(10,2),
det_prec_vent_t numeric(10,2) DEFAULT 0,
det_desc integer,

CONSTRAINT detalle_det_cod_doc_fkey FOREIGN KEY (det_cod_doc)
  REFERENCES public.documento (doc_cod) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT detalle_det_cod_prod_fkey FOREIGN KEY (det_cod_prod)
  REFERENCES public.productounidad (prod_cod) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT detalle_det_nombre_uni_fkey FOREIGN KEY (det_nombre_uni)
  REFERENCES public.productounidad (uni_nombre) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION
)

I get the following error:

there is no unique restriction that matches the columns given in the table referred to as «productunit»

Try adding unique in the primary primary line but it does not allow me

postgresql

asked by Leo T 01.11.2017 в 16:35

source

1 answer

Can not truncate table 'Table' because it is being referenced by a FOREIGN KEY constraint Android Multi-thread Attempt in TextWatcher

score 2 · Accepted Answer

In this case, it seems to me that you intend to define a foreign key for the combination of (det_cod_prod, det_nombre_uni) columns pointing to the primary key in the productounidad table. That can be done.

But separately, as you do, it is not valid, because the individual columns are not unique in the productounidad table.

Replace the 2 foreign keys with only this one:

CONSTRAINT detalle_det_cod_prod_nombre_uni_fkey FOREIGN KEY (det_cod_prod, det_nombre_uni)
  REFERENCES public.productounidad (prod_cod, det_nombre_uni) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION