NULL value in multi-column primary key
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
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
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.
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.