alter table: only allow either unique or empty

Question

I have an existing mysql table, and I want to set one field (varchar) so that it must either be unique, or empty (by default). So when user does not enter value, its empty, otherwise user must enter unique value.

how would I do that ?

Add unique constraint to that field and make it default null. — N.B., Sep 05 '13 at 12:21
You can't use empty values on a unique index but you can use `null`. — Rob, Sep 05 '13 at 12:21

DiMono · Accepted Answer · 2013-09-05T12:33:53.897

2

MySQL includes a constraint type UNIQUE that allows for exactly this. If you declare a column UNIQUE and allow NULL values in it, then all values will be forced to be unique unless they are NULL values. If you want to prevent empty strings ('') from being stored, you will have to manage that at the code level, because MySQL will treat it as just another string and allow it once in a UNIQUE column. If no value is passed to such a column, it will automatically default to NULL, but you can also specify NULL (not in quotes) programmatically in your code before passing the values to the database.

For the rest of this post I will assume the name of the table is tablename and the name of the column is columnname and the size of the varchar field is 255

If you want to modify an existing table, rather than create a new one:

If the column does not already support null values:

ALTER TABLE tablename ALTER COLUMN columnname VARCHAR(255) NULL

To add the unique constraint:

ALTER TABLE tablename ADD CONSTRAINT un_tablename_column UNIQUE (columnname)`

(I always name my constraints so I can refer to them specifically later)

OR if you want to do it on a new table:

CREATE TABLE tablename (
    [...]
    columnname varchar(255) UNIQUE NULL,
    [...]
)

edited Sep 05 '13 at 12:33

answered Sep 05 '13 at 12:28

DiMono

3,308
2
19
40

pretty sure this is not true for mysql in general, it probably is a special engine setting you need to be aware of, if it was not that case there should be no reason why this fiddle fails at the second insert http://sqlfiddle.com/#!2/c5d313 – DrCopyPaste Sep 05 '13 at 13:42
my bad, sqlfiddle wont let me save a fiddle that fails: http://sqlfiddle.com/#!2/b3a93 you need to drop the comments in this to see what I described in the last comment – DrCopyPaste Sep 05 '13 at 13:59
It fails because you forgot the semicolon between statements. – DiMono Sep 05 '13 at 14:21
aaarg, I am so sorry, I take it all back, bad habit from sql server :) (but still weird behavior^^) – DrCopyPaste Sep 05 '13 at 14:24
Not really: think of it as "unique if present" – DiMono Sep 05 '13 at 14:41

score 1 · Answer 2 · edited May 23 '17 at 12:20

From what I have read, by standard it should not be possible to have a column in a table that allowes null values AND has a unique constraint; if it is, it is down to a special mysql-engine (that will result in unpredictable/unexpected behavior elsewhere also imho)

Also your question seems kind of reverse of this one

MySQL: UNIQUE, but DEFAULT NULL - allowed by creating of table. More than 1 NULL is allowed to insert. Why?

You could however enforce this using triggers, you can write a trigger on insert/update to check if the value already exists and immediatly delete it if it does.

What exactly shouldn't be possible? – ypercubeᵀᴹ Sep 05 '13 at 12:28 — ypercubeᵀᴹ, Sep 05 '13 at 12:28

score 0 · Answer 3 · answered Sep 05 '13 at 12:24

0

I think you better process your data before pass to mysql, or not, you can write a trigger trigger whenever your table is update to make sure it pass your rule

answered Sep 05 '13 at 12:24

Hardy

1,499
2
26
39

alter table: only allow either unique or empty

3 Answers3