NULL value in multi-column primary key

67
votes

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?

5
mysqlnullcomposite-primary-key
Thanks to vj shah's link, @Tomalak makes the excellent point 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
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

5 Answers

61
votes

From the MySQL documentation :

A PRIMARY KEY is 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.

http://dev.mysql.com/doc/refman/5.1/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.

29
votes

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

Inorder to insert insert null values make field2 as Unique

Unique constraint make the field removes duplicates but allow null values

8
votes

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!

6
votes

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)
3
votes

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/