Mysql - Update to null Unique column

Asked

Viewed 714 times

2

Good afternoon, you guys.

I would like to know if there is a possibility of UPDATE for NULL where the field allows only values UNIQUE.

I tried that way:

update FUNCIONARIO set codigocartao = NULL where situacao = 'DESATIVADO'

But returned me duplicate registry error.

The scenario I need is, the code field should have a unique record, but it can also be null. the scenario I have today is: every time I need to set the field to NULL, I end up having to delete the table.

I’m doing something wrong or this possibility does not exist in MySQL? Does anyone have any idea?

mysql

  • In theory it is possible pq one null is never equal to another. codigocartao is FK too? could put original error message? and table setting.

  • 1

    rray, no, the field was set with UNIQUE and NOT NULL (this the problem). thanks.

1 answer

1


Yes, you can do that. see the Mysql documentation (version 5.5).

The UNIQUE index creates a Constraint such that all values in the index must be distinct. An error occurs if you Try to add a new Row with a key value that Matches an existing Row. For all Engines, a UNIQUE index Allows Multiple NULL values for Columns that can contain NULL.

Make sure you have not set the column to NOT NULL

Browser other questions tagged mysql

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