PostgreSQL allows a column to be null even though it has a UNIQUE
constraint since the NULL
value is exceptional and never equal to other nulls. There's nothing to stop what you're doing.
You would only have problems if you try to do this in primary key, which is not the case.
There is a current that a table should never have nulls. This is a bit radical but it makes some sense. Analyze and see what works best for you.
Documentation .
Just an addendum, I do not see with good eyes the use of type int
to save RG or CRM, these data are not numbers, they just happen to have digits. Actually the RG has characters. Integer should be used when you have real numerical values, when you will do calculations with it and are not being used only as identifiers, descriptors of an information. The same criterion that led you to make the CPF be varchar
and maybe could be up to char
, should guide the other fields identifiers. It looks like you are going to record the points and dash in the CPF, this is also not a good idea, this is formatting and not information. Formatting should not go down in the database.
It would look something like this:
CREATE TABLE TBPessoa (
id integer,
nome varchar(45),
nascimento datetime,
rg char(11),
cpf char(11),
sexo char(1),
crm char(8),
UNIQUE (crm)
);