Use composite key as PK or not?

Asked

Viewed 117 times

0

I have two tables:

1- Contracted Service

2- Payment Contracted Service

1 service may have N payments. I modeled the Contracted Service Payment class as follows:

CREATE TABLE public.pagamento_servico_contratado
(
  id integer NOT NULL DEFAULT nextval('pagamento_servico_contratado_id_seq'::regclass),
  id_servico_contratado integer NOT NULL,
  codigo_forma_pagamento character varying NOT NULL,  
  data date,
  valor numeric,
  observacao character varying
)

I’m using the sequential id for the primary key. Regarding integrity this is correct or should use a composite key as PK being the payment id and the id of the contracted service?

  • 1

    It seems correct, but without knowing the requirements we have no way of knowing. Right or wrong does not depend on the database but on the requirement.

  • If a combination of the fields of your table must uniquely identify each row of your table then you must place this integrity restriction in the table definition. The way you have defined nothing prevents multiple payments from being entered, each with a different id as it will be auto-incremented, but in the real world refer to the same payment. In Postgresql the type is used SERIAL (or BIGSERIAL) for this type of field.

1 answer

1

I don’t think it’s necessary to create a composite primary key in your pay table.

If a Serviço Contratado may have multiple Pagamentos, to cardinality between these entities would be one-to-several or 1:N.

A foreign key is only able to reference primary keys, hence its Serviços Contratados you will need a primary key Constraint for that table Pagamentos is able to reference it.

Like good practice, both tables require a primary key, which could guarantee that there would be no identical/duplicate records in the same table.

I suggest a modelling more or less like this:

inserir a descrição da imagem aqui

The SQL script equivalent to this modeling would be something like:

CREATE SEQUENCE public.pagamento_servico_contratado_id_seq;
CREATE SEQUENCE public.servico_contratado_id_seq;

CREATE TABLE public.servico_contratado
(
  id integer NOT NULL DEFAULT nextval('servico_contratado_id_seq'::regclass),
  data date,
  descricao character varying,
  PRIMARY KEY (id)
);

CREATE TABLE public.pagamento_servico_contratado
(
  id integer NOT NULL DEFAULT nextval('pagamento_servico_contratado_id_seq'::regclass),
  id_servico_contratado integer NOT NULL,
  codigo_forma_pagamento character varying NOT NULL,  
  data date,
  valor numeric,
  observacao character varying,
  PRIMARY KEY (id),
  FOREIGN KEY (id_servico_contratado) REFERENCES public.servico_contratado (id)
);

Browser other questions tagged

You are not signed in. Login or sign up in order to post.