Prohibit lines from being exactly the same in the Database

Navigation
0

I have a system that integrates data from three different banks, more specifically the business register of these three banks, I created a table where the user integrations stored are stored, for example: 

no banco X a empresa A é código 01
no banco Y a empresa A é código 02
no banco Z a empresa A é código 03

In my table that stores the integrations that the user is going to make the data would look something like this: 

nomeEmpresa = A
codigoBancoX = 1
codigoBancoY = 2
codigoBancoZ = 3

I need to avoid having to add exactly the same fields to all fields, ie I can not have two lines in my table exactly the same. My question is. If I put all the fields in my table as Primary Key would I solve my problem? Well, no line could be exactly the same, but there could be a line like this: 

nomeEmpresa = A
codigoBancoX = 1
codigoBancoY = 2
codigoBancoZ = 3

nomeEmpresa = A
codigoBancoX = 1
codigoBancoY = 2
codigoBancoZ = 4
    
asked by anonymous 22.06.2017 / 16:57

3 answers

2

As stated in the comments, the correct one in your situation is to use a single index.

Create a UNIQUE INDEX in postgresql: 

CREATE UNIQUE INDEX tabela_unique_idx ON public.tabela
  USING btree ("col1", "col2", "col3");

Reference: link

    
22.06.2017 / 17:06
0

PK uniquely identifies each record in a database table, UNIQUE recommended for what you need. And you have to do the error handling in your application when the records are identical.

ERROR 1062: 1062: Duplicate entry 'value' for key 'column_UNIQUE'

    
22.06.2017 / 17:09
0

By its description, I believe you need to have each field have a unique value, after all you should not have two companies in banco X with same id .

To do this, you must create a unique Restriction for each field.: 

ALTER TABLE public.Empresas ADD CONSTRAINT CK_Empresas_codigoBancoX UNIQUE (codigoBancoX);
ALTER TABLE public.Empresas ADD CONSTRAINT CK_Empresas_codigoBancoY UNIQUE (codigoBancoY);
ALTER TABLE public.Empresas ADD CONSTRAINT CK_Empresas_codigoBancoZ UNIQUE (codigoBancoZ);

Your second option is to use a unique index 

CREATE UNIQUE INDEX IXU_Empresas_codigoBancoX ON public.Empresas (codigoBancoX);
CREATE UNIQUE INDEX IXU_Empresas_codigoBancoX ON public.Empresas (codigoBancoY);
CREATE UNIQUE INDEX IXU_Empresas_codigoBancoX ON public.Empresas (codigoBancoZ);

I would say to create an index if this code is used as a condition in some query.

    
22.06.2017 / 17:17
Correcting or preventing display of "Script Error" [closed] FriendlyChat - date in messages [closed]