Re: Question related to unique key

[laptop alias]

A UNIQUE KEY uniquely identifies rows in a table.

A PRIMARY KEY is a special kind of UNIQUE KEY. Where UNIQUE KEYS (or components within them) can be NULL, PRIMARY KEYS cannot.

Further, a table can have many UNIQUE KEYS but only one of these can be declared PRIMARY.

The allowance of a NULL values in a UNIQUE KEY does seem contradictory however:

CREATE TABLE `example` (
  `control_id` int(11) NOT NULL default '0',
  `p_val` int(11) NOT NULL default '0',
  `u_val` int(11) default NULL,
  PRIMARY KEY  (`control_id`,`p_val`),
  UNIQUE KEY `control_id` (`control_id`,`u_val`)
);

INSERT INTO example VALUES (0,0,NULL),(0,1,NULL),(0,2,NULL);

SELECT * FROM example;
+------------+-------+-------+
| control_id | p_val | u_val |
+------------+-------+-------+
|          0 |     0 |  NULL |
|          0 |     1 |  NULL |
|          0 |     2 |  NULL |
+------------+-------+-------+

Edited 1 time(s). Last edit at 03/16/2009 08:33AM by laptop alias.