Could you add a UNIQUE field that accepts nulls?

Asked

Viewed 2,019 times

3

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.

inserir a descrição da imagem aqui

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

That was my first model

inserir a descrição da imagem aqui

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

inserir a descrição da imagem aqui

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.

  • If I remember the modeling classes correctly, you should create a specialization table of people and there would be crm

  • 1

    Yes you can because a value nulo is never the same as another. Try to explain the scenario where this will be applied.

  • I understand, but maybe I should change my modeling

  • 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".

  • I edited the question, take a look

1 answer

6


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.

  • Look at the Edit I made

  • Look, do you want me to do something with this information? It doesn’t seem to add anything to the original question. If you want to ask a new question, open a new question.

  • Okay, I was making the question a mess, but the answer helped me, and the null question, I agree, I organized it better, and I solved the problem, thank you for the answer

  • @Related Lucasmotta: http://answall.com/q/53296/101

Browser other questions tagged

You are not signed in. Login or sign up in order to post.