Prohibit lines from being exactly the same in the database

Asked

Viewed 43 times

0

I have a system that integrates data from three different banks, more specifically the registration of the companies of these three banks, I created a table where are stored the integrations that user will do, 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 user will do the data would look like this:

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

I need to avoid that by carelessness of the user be added exactly equal all fields, ie can not have two rows in my table exactly equal. My question is. If I put all fields of my table as Primary Key would solve my problem? Because then no line could be exactly the same but still could exist line like this: 

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

nomeEmpresa = A
codigoBancoX = 1
codigoBancoY = 2
codigoBancoZ = 4
database primary-key

  • Don’t use PK for this, create a UNIQUE key with relevant columns.

  • How do I do this in Postgresql you could tell me? I thought that PK was for this, so that it was only the column

  • Related: It is possible to have more than one Primary key in a table?

  • 1

    In Postgres I do not know, but surely there is someone here who does. PK is the primary key, the main identifier of the line. It’s usually not cool that it’s too complex. Semantically it’s different from UNIQUE (although it implies UNIQUE).

  • @R.Santos feel free to use one Indice Unico or a Restriction Unica, if you want to know the difference between the two... CLICK HERE

3 answers

2


  • That way I can add for example: codigoBancoX = 1 | codigoBancoy = 2 | codigoBancoZ = 3 on one line and on the other line codigoBancoX = 1 | codigoBancoy = 2 | codigoBancoZ = 4?

  • yes, can, the single key with the, in your case, 3 columns, will only be breached if all columns are equal.

  • Perfect then, last thing I can execute this command after my table is already created?

  • yes, only will not be able to execute if there is already data that violates this restriction.

  • Right. And this one USING btree is standard or has a specific reason for you to have used?

  • that’s the index method, and yes the pattern, which is binary tree. There are also the methods gin, hash and gist, for more information on the reply of the bigown in this question: https://answall.com/questions/101065/o-que-s%C3%A3o-os-index-b-Tree-hash-gist-e-gin

Show 1 more comment

0

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

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

0

From your description, I believe you need each field to have a unique value, after all you shouldn’t have two companies in the banco X with the same id.

To do this, you must create a unique Constraint 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 Intel

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);

would say to create an Index if this code is used as a condition in any query.

  • That way I can add for example: codigoBancoX = 1 | codigoBancoy = 2 | codigoBancoZ = 3 on one line and on the other line codigoBancoX = 1 | codigoBancoy = 2 | codigoBancoZ = 4?

  • No, because it violates the restriction in code x and y.

  • But what I need is this, I have to be able to repeat the codes only I can’t have the lines exactly equal

  • Is there any way I can do this?

  • @R.Santos by the description of its application, it seems to me that its bank is badly modeled.

  • But what you think I need to change @Tobias Mesquita?

  • In your example, if a company can have more than one id in the banco z, then it would be better to keep a table with a 1:N relationship instead of keeping all combinations in a single table.

  • The problem is that this data is coming from other banks

Show 3 more comments

Browser other questions tagged database primary-key

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