Could you add a UNIQUE field that accepts nulls?

3

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

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

I'mstilldesigningadiagramandthenI'llcreateitinPostgreSQL.

Thiswasmyfirstmodel

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

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

    
asked by anonymous 05.03.2015 / 19:40

1 answer

5

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)
);
    
05.03.2015 / 19:49