3

Following is the text from the MySQL Documentation :

The primary key for a table represents the column or set of columns that you use in your most vital queries. It has an associated index, for fast query performance. Query performance benefits from the NOT NULL optimization, because it cannot include any NULL values.

I am not understanding the exact meaning of the sentence in bold font from the above text. Someone please explain to me.

Also, let me know whether can we create an index on a column containing NULL values in MySQL 5.7? If no, what's the reason?

Thank You.

Improve this question
6
  • a PRIMARY key cannot have NULL values, but other indexes can be created on columns with NULL values. Commented Dec 10, 2017 at 4:31
  • @Used_By_Already : What if the primary key contains more than one columns? It it called composite key if the primary key contains more than one column? Commented Dec 10, 2017 at 4:37
  • 1
    correct, a composite key contains more than one column, but NO PART of a composite (primary) key can be null either. Only non-primary indexes allow columns to have NULLs Commented Dec 10, 2017 at 4:41
  • In case of composite key, is it mandatory that the columns involved can only have unique and not-null values? Commented Dec 10, 2017 at 4:47
  • 2
    a composite primary index must form a unique combination of values and no nulls are permitted Commented Dec 10, 2017 at 4:54

1 Answer 1

Reset to default
3

A PRIMARY key is used to organize the data on disk and because there is a relationship to how the data is physically arranged you cannot have any part of a primary key being NULL.

A non-primary index CAN have one or more columns that allow NULLs. demo

CREATE TABLE `my_docs` (
  `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `rev` int(3) ,
  `content` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;


INSERT INTO `my_docs` (`rev`, `content`) VALUES
  (1, 'The earth is flat'),
  (1, 'One hundred angels can dance on the head of a pin'),
  (NULL, 'The earth is flat and rests on a bull\'s horn'),
  (3, 'The earth is like a ball.');

alter table `my_docs` add key my_added_index (`rev`);

SELECT id, rev, content
FROM `my_docs`
where rev = 3

| id | rev |                   content |
|----|-----|---------------------------|
|  4 |   3 | The earth is like a ball. |


+---+-------------+-----------+------+----------------+----------------+---------+-------+------+----------+-------+
| d | select_type |   table   | type | possible_keys  |      key       | key_len |  ref  | rows | filtered | Extra |
+---+-------------+-----------+------+----------------+----------------+---------+-------+------+----------+-------+
| 1 |    SIMPLE   |   my_docs | ref  | my_added_index | my_added_index |       5 | const |    1 |   100.00 |       |
+---+-------------+-----------+------+----------------+----------------+---------+-------+------+----------+-------+
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.