Index - Primary Key

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
dstefani
Forum Contributor
Posts: 140
Joined: Sat Jan 11, 2003 9:34 am
Location: Meridian Idaho, USA

Index - Primary Key

Post by dstefani »

I had a question about indexing.

It is my understanding that defining a column as primary key automatically indexes that column and enforces a unique constraint without the need for another index. So I wouldn't have to include the primary key in any indexes I add to this table?

Thanks,

dstefani
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

You can create 4 kinds on index for a table - Primary, Index, Unique, Fulltext
For Primary index, the name of the index has to be Primary.
Check the CREATE INDEX syntax.
So I wouldn't have to include the primary key in any indexes I add to this table?
What do you mean ?
User avatar
dstefani
Forum Contributor
Posts: 140
Joined: Sat Jan 11, 2003 9:34 am
Location: Meridian Idaho, USA

Post by dstefani »

Is this code correct?

Code: Select all

CREATE TABLE `cat_catalogs` (
  `ca_id` int(4) NOT NULL auto_increment,
  `ca_cm_id` int(3) NOT NULL default '0',
  `ca_title` varchar(50) collate latin1_general_ci NOT NULL default '',
  `ca_sort_order` int(4) NOT NULL default '0',
  `ca_description` text collate latin1_general_ci NOT NULL,
  `ca_image` varchar(100) collate latin1_general_ci default NULL,
  `ca_image_2` varchar(100) collate latin1_general_ci default NULL,
  `ca_active` int(1) NOT NULL default '0',
  `ca_bullion` int(1) NOT NULL default '0',
  `ca_uid` varchar(100) collate latin1_general_ci NOT NULL default '',
  `ca_sort_1` varchar(12) collate latin1_general_ci NOT NULL default '',
  `ca_sort_2` varchar(12) collate latin1_general_ci NOT NULL default '',
  PRIMARY KEY  (`ca_id`),
  UNIQUE KEY `ca_id` (`ca_id`),
  KEY `ca_sort_order` (`ca_sort_order`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci PACK_KEYS=0
Am I being redundant adding "UNIQUE KEY 'ca_id' " since it's already the primary?

Thanks,

- dstefani
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

being primary automatically means unique.
Post Reply