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:
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)
);
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.
– Maniero
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(orBIGSERIAL) for this type of field.– anonimo