NULL value in multi-column primary key

Question

I've got a table with several columns making up the primary key. The nature of the data stored allows some of these fields to have NULL values. I have designed my table as such:

CREATE TABLE `test` (
    `Field1` SMALLINT(5) UNSIGNED NOT NULL,
    `Field2` DECIMAL(5,2) UNSIGNED NULL DEFAULT NULL,
    PRIMARY KEY (`Field1`, `Field2`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

However, when I run describe test it shows like this:

|| *Field* || *Type*                || *Null* || *Key* || *Default* || *Extra* 
|| Field1  || smallint(5) unsigned  || NO     || PRI   ||           ||         
|| Field2  || decimal(5,2) unsigned || NO     || PRI   || 0.00      ||

And I keep getting an error when inserting a NULL value.

Column 'Field2' cannot be null

Is this because a field that is part of a primary key cannot be null? What are my alternatives besides using, say, '0' for NULL?

Thanks to vj shah's link, @Tomalak makes the [excellent point](http://stackoverflow.com/a/386061/673991) that this restriction follows from the basic SQL tenet that, since a PRIMARY key's parts must be compared from each row to every other row, and "**NULL cannot be part of a comparison - the result of such a comparison would always be NULL**" that enforcing PRIMARY key uniqueness requires non-null columns. — Bob Stein, Mar 31 '13 at 15:40
For string, use empty string (or, make it default) for one of the columns, so one record can be inserted without any issue. Subsequent inserts need to have some value to make sure uniqueness of composite key (multi column primary key). — Manohar Reddy Poreddy, May 03 '21 at 11:32

score 70 · Accepted Answer · edited Nov 08 '21 at 18:05

70

From the MySQL documentation :

PRIMARY KEY

A unique index where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). A table can have only one PRIMARY KEY. The name of a PRIMARY KEY is always PRIMARY, which thus cannot be used as the name for any other kind of index.

https://dev.mysql.com/doc/refman/8.0/en/create-table.html

If Field2 can be NULL, I question why you need it as part of the Primary Key since you then need Field1 to be distinct across all rows. So Field1 by itself should be sufficient as the Primary Key. You could create a different type of index on Field2.

edited Nov 08 '21 at 18:05

Cave Johnson

6,499
5
38
57

answered Jun 12 '12 at 17:06

Girish Rao

2,609
1
20
24

20

Surprising that it allows one to create a primary key using a column that is defined as accepting nulls. – Marvo Jun 12 '12 at 17:07
2

That's the definition I was looking for but didn't find. Thank you. In fact I've got 12 columns that make up the primary key right now. 5 of these values are nullable. It is a caching strategy where not all values are needed in the cashed request. My idea was that a composite primary keys would be cheaper than using an autoincrement value as PK and adding a unique index over all the others. Looks like I have to go that way. – simbabque Jun 13 '12 at 07:14
16

@Girish I've just run into this problem and I also want to have one nullable field. In my case, the PK is two fields, one of them nullable, but I'd like at most one row with the 2nd field NULL, so that should be a valid PK. IMHO, this is a failed spec from MySQL. – Seb Oct 25 '13 at 19:29
2

I have the same problem, I need a two column pk with one null. This column it is also a foreign key that may link to another table. Without null option I must set it with a value and it broke the foreign key. – Tobia Jun 25 '14 at 08:41
20

I don't see it. Clearly Field1 -> 5 and Field2 -> null is different from Field1 -> 5 and Field2 -> 3. So that could be used to identify univocally different pairs. I found this thread after googling because I'm in that situation, and don't understand why I cannot use NULL in a primary key... – Pere Jul 09 '14 at 12:40
To have a more detailed explaination on why it's possible on unique keys and not on primary, look a the second answer to the question linked in viyay answer below – Matthieu Jan 07 '16 at 08:45

score 31 · Answer 2 · edited Sep 28 '21 at 08:10

31

Primary keys are used to make the column both unique and not null

In order to insert NULL values make field2 as UNIQUE.

Unique constraint makes the field removes duplicates but allows null values

edited Sep 28 '21 at 08:10

Ivan Olshansky

889
2
17
23

answered May 06 '15 at 09:40

Lordferrous

678
8
8

4

This answers to the question, thank you. The accepted answer was wrong. – singe3 Jun 17 '15 at 12:29
2

@singe3 well, the question was not _how do I create an index with a NULL value_, but _why can I not have a NULL value in the primary key_. I'm glad that the information that a unique key lets you have a NULL field helped you, but it does not answer the question. The accepted answer does. – simbabque Apr 26 '17 at 17:22
technically, you'd want to create a unique constraint/index on field1 AND field2, and probably add a surrogate key for a primary key (so that you have one). – Jeremy Giaco Apr 16 '23 at 18:52

score 8 · Answer 3 · edited May 23 '17 at 12:34

8

Primary key states that column mustn't have NULL values. So columns used for defining composite primary key isn't going to be NULL.

Also Oracle server compares the combination of all columns used in a composite primary key definition. If your all columns existing data (say x,y) matched with newly adding row, it will raise error of Unique Constraint Violated.

Moreover,look at this thread: What's wrong with nullable columns in composite primary keys?.

This link provides valuable information regarding possibility of NULLABLE columns in composite key!

edited May 23 '17 at 12:34

Community

1
1

answered Jun 12 '12 at 18:28

vijay

2,034
3
19
38

3

Thanks for your input. I'm not on Oracle but on MySQL though. – simbabque Jun 13 '12 at 07:17

score 7 · Answer 4 · answered Aug 20 '14 at 12:32

You can use unique key like this:

mysql> CREATE TABLE `test` (
    ->     `Field1` SMALLINT(5) UNSIGNED NOT NULL,
    ->     `Field2` DECIMAL(5,2) UNSIGNED NULL DEFAULT NULL,
    ->     UNIQUE KEY (`Field1`, `Field2`)
    -> )
    -> COLLATE='latin1_swedish_ci'
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql> 
mysql> desc test
    -> ;
+--------+-----------------------+------+-----+---------+-------+
| Field  | Type                  | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| Field1 | smallint(5) unsigned  | NO   | MUL | NULL    |       |
| Field2 | decimal(5,2) unsigned | YES  |     | NULL    |       |
+--------+-----------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

score 3 · Answer 5 · answered Jun 12 '12 at 17:08

3

you can use unique keys, please take a look to this link, they work with null values

http://www.xaprb.com/blog/2009/09/12/the-difference-between-a-unique-index-and-primary-key-in-mysql/

answered Jun 12 '12 at 17:08

jcho360

3,724
1
15
24

NULL value in multi-column primary key

5 Answers5

Linked

Related