Mysql unique field will not accept multiple nulls

Question

I have a table containing a video_id field which is set to unique accept NULL values. However it will not accept two null values. Table type InnoDB or MYISAM will not work.

This produces a duplicate key error on video _id (Null value):

INSERT INTO dances (name, video_id, level) VALUES ('abc', '', 'beg');

If I read the Mysql rules correctly it should do so. Can anyone suggest where to go next?

Show a small example (`CREATE TABLE`, `INSERT` a few rows) where this occurs. You can use **[sqlfiddle.com/](http://sqlfiddle.com/)** — ypercubeᵀᴹ, Jun 19 '13 at 08:13
Have you by chance added a `DEFAULT x` attribute to the column? — ypercubeᵀᴹ, Jun 19 '13 at 08:15

score 2 · Answer 1 · edited May 23 '17 at 11:56

2

Yes, MySQL allows multiple NULLs in a column with a unique constraint. See this Stack Overflow question.

An empty string '' is not the same as NULL.

Use this instead:

INSERT INTO dances (name, video_id, level) VALUES ('abc', NULL, 'beg');

edited May 23 '17 at 11:56

Community

1
1

answered Jun 19 '13 at 07:56

Praveen Prasannan

7,093
10
50
70

INSERT INTO `dances`( `name`, `video_id`, `level`) VALUES ('abc', '', 'beg') this produces a duplicate key error on video _id (Null value) – Terry Wright Jun 19 '13 at 08:16
3

@TerryWright An empty string `''` is not the same as `NULL`. – ypercubeᵀᴹ Jun 19 '13 at 08:52
@TerryWright: Use this: INSERT INTO dances( name, video_id, level) VALUES ('abc', null, 'beg') – Praveen Prasannan Jun 19 '13 at 09:12

score 1 · Answer 2 · answered Jun 19 '13 at 07:29

1

You could try BDB (about which I know nothing); see here for more.

answered Jun 19 '13 at 07:29

Brian Hooper

21,544
24
88
139

Didn't know that changing the underlying engines enabled this... – Paul Jun 19 '13 at 07:31
This reference seems to support my understanding that in InnoDB multiple Nulls should work. Thanks for the ref anyway. – Terry Wright Jun 19 '13 at 08:06

score 0 · Answer 3 · edited Jun 19 '13 at 07:34

0

And why do you need multiple rows of your table to have a null video_id?

From a DB design perspective, it sounds like the object you are modelling with the table is too "big" and needs to be broken up into a main table and detail tables.

Your options are (A) remove the UNIQUE constraint, or (B) create a separate table that contains object_id, video_id pairs (where object_id is whatever the primary key is on your main table).

edited Jun 19 '13 at 07:34

Subhra Sekhar Mukhopadhyay

156
14

answered Jun 19 '13 at 07:28

Paul

26,170
12
85
119

The table is a very simple one :- id, dance name, Video-id, dance level. The id refers to youtube video of the dance but some don't have one. In practical terms I could do away with the unique constraint but I don't understand why the Nulls produce a Duplicate Key failure. According to the Mysql info it should be ok. Thanks for the comments. – Terry Wright Jun 19 '13 at 08:13
@TerryWright From a data modelling perspective, each row of a table of dances describes the properties of a dance. When a dance has 0 or more of something else, that is not really a property of a dance but a "has a" relationship that often gets a separate table. Perhaps in the system you are developing there is only 0 or 1 videos per dance, and it might be desirable to keep things simple, but a data modeller might say, hey, a dance could have lots of videos, and a long video could also show several dances, possibly necessitating a table of (dance_id,video_id) pairs as described. – Paul Jun 20 '13 at 09:00

Mysql unique field will not accept multiple nulls

3 Answers3