Non-uuid foreign key cannot be implemented

Asked

Viewed 53 times

0

I am creating tables in a Postgre database, in the tables I use UUID as the primary key. For that, I executed: CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
So I created the first table with:

DROP TABLE IF EXISTS documentos;
CREATE TABLE IF NOT EXISTS documentos (
  id uuid DEFAULT uuid_generate_v4 (),
  nome VARCHAR (64) NOT NULL,
  ativo INTEGER NOT NULL DEFAULT 1,
  created_at VARCHAR (32),
  updated_at VARCHAR (32),
  UNIQUE(id)
);

The next table should be as follows:

DROP TABLE IF EXISTS diretores;
CREATE TABLE IF NOT EXISTS diretores (
  id uuid DEFAULT uuid_generate_v4 (),
  nome VARCHAR (64) NOT NULL,
  email VARCHAR (64) NOT NULL UNIQUE,
  ativo INTEGER NOT NULL DEFAULT 1,
  created_at VARCHAR (32),
  updated_at VARCHAR (32),
  inicio VARCHAR (12),
  documento VARCHAR (64),
  documento_id INTEGER REFERENCES documentos(id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION,
    UNIQUE(id)
);

But when running the above SQL error is returned to me: foreign key constraint "diretores_documento_id_fkey" cannot be implemented.
Why this key cannot be implemented, since I have declared that it will be unique and the extension has already been declared?

  • And why do you do it? The biggest reason to use UUID is not to have to generate the id on the server, when doing so has disadvantages that rarely makes up for the use, can justify the choice?

  • So @Maniero, this was an indication I received from a teammate. Ideally create the id column as text and insert the UUID via code?

  • Check your foreign key declaration. The data type uuid has 128 bits while a int has 32 bits (4 bytes).

  • I noticed that in documento_id INTEGER REFERENCES documentos(id) MATCH SIMPLE
 ON UPDATE NO ACTION ON DELETE NO ACTION I’m actually declaring INTEGER, like I said. I declared as uuid type, but I’m intrigued by what @Maniero said and would like to do using good practices, the bank is very large and I don’t want to weigh in on the performance.

  • @Filipemachado the ideal for your case I do not know, can be, indications are always complicated because you do not know if the person knows what is saying, if she just learned a cake recipe and is passing without thinking about what it is and especially if it serves for your case. Here are several questions on the subject with answers well evaluated by competent professionals and can make a better decision than following what someone said. It may not even be the case to generate a UUID.

  • So @Maniero, in this case I really need it to be UUID because the application serves an internal network as well as an external network, otherwise there will be conflicts when the data is found. Thanks for the tips, will surely serve me as a source to find the right path and database is not my strong point.

  • @Filipemachado no, the data bank does not even know if it is internal or external network, what works in one works in the other equal. This makes no sense. The way you create it is that it changes, if you do it right id normal is valid and more advantageous. UUID is required if you do not want the server to create the id. In this case you have the worst of both worlds, the disadvantages of having the id sequential and the disadvantages of using UUID. There is reason to use UUID in other situations, but it does not seem to be the case, so it did not justify in a way that shows this.

  • Unless you’re talking about different databases that are merged. But then I ask if you really need to do that, because it’s a very complicated thing to manage, it’s not just the id that may conflict. Even a case like this if it really cannot be otherwise, UUID can be avoided (it is no longer clear which strategy is the mis acceptable in the scenario). Still in this case may be better generate in the application.

  • @Maniero understood your point of view and really is a very strong argument, I will review the inclusion of UUID. Thank you very much. Please make your statement regarding the solution so I can upvote and close the question.

Show 4 more comments
No answers

Browser other questions tagged

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