Could you add a UNIQUE field that accepts nulls?

Question

I can create a field that accepts nulls and say it is unique?

This table of mine, the CRM indicates that he is a doctor and not a normal person, he has to be unique, but has to allow nulls. This field is also a foreign key in another table.

I’m still designing a diagram and then create in Postgresql.

That was my first model

Looking at the comments, I made some changes and stayed like this

But with this link between people and expertise, it seems to me that CRM can be repeated to different people, I may be wrong, but this impression that was.

Answer 1

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.