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?– Maniero
So @Maniero, this was an indication I received from a teammate. Ideally create the id column as text and insert the UUID via code?
– Filipe Machado
Check your foreign key declaration. The data type
uuid
has 128 bits while aint
has 32 bits (4 bytes).– anonimo
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.– Filipe Machado
@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.
– Maniero
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.
– Filipe Machado
@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 theid
. In this case you have the worst of both worlds, the disadvantages of having theid
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.– Maniero
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
@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.
– Filipe Machado