Postgresql allows a column to be null even if it has a UNIQUE
since the value NULL
is exceptional and is never equal to other nulls. There is nothing to stop what you are doing.
You would only have problems if you tried to do this in primary key, which is not the case.
There is a current that argues that a table should never have nulls. This is a bit radical but it makes some sense. Analyze and see what will work best for you.
Documentation.
Just an addendum, I don’t take kindly to the use of the type int
To save RG or CRM, this data is not numbers, they happen to only have digits. Actually the RG has characters. Integers should be used when you have real numerical values, when you will do calculations with them and are not being used only as identifiers, descriptors of information. The same criteria that led you to make the number varchar
and maybe it could be char
, should guide the other identifier fields. It seems that you will record the dots and dash in the CPF, this is also not a good idea, this is formatting and not information. Formatting should not go to the database.
It would be something like that:
CREATE TABLE TBPessoa (
id integer,
nome varchar(45),
nascimento datetime,
rg char(11),
cpf char(11),
sexo char(1),
crm char(8),
UNIQUE (crm)
);
I put in the Github for future reference.
If I remember the modeling classes correctly, you should create a specialization table of people and there would be crm
– Luis Henrique
Yes you can because a value
nulo
is never the same as another. Try to explain the scenario where this will be applied.– rray
I understand, but maybe I should change my modeling
– LucasMotta
Could show what would be the field related
crm
with the other table? I also suggest adding the tag modeling, at least when I saw the question I focused on the "I can have a null field" part and it seems your question is "I’m going to have some problem with a null key in this modeling".– rray
I edited the question, take a look
– LucasMotta