MySQL Forums
Forum List  »  Newbie

Re: Question related to unique key
Posted by: laptop alias
Date: March 16, 2009 08:13AM

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.

Options: ReplyQuote

Written By
March 16, 2009 07:43AM
Re: Question related to unique key
March 16, 2009 08:13AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.