importing table and getting error. need help

sarmenhb · 02-05-2008, 11:01 PM

hi

this is the table structure that i'm querying
and the bug that i'm getting is:

SQL Error: Incorrect table definition; There can only be one auto column and it must be defined as a key

how can i fix it, anyone see the problem?
thanks

Code:

CREATE TABLE `prospects` (
  `id`                          varchar(36) NOT NULL,
  `tracker_key`                 int AUTO_INCREMENT NOT NULL,
  `deleted`                     tinyint(1) DEFAULT '0',
  `date_entered`                datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_modified`               datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modified_user_id`            varchar(36),
  `assigned_user_id`            varchar(36),
  `created_by`                  varchar(36),
  `salutation`                  varchar(5),
  `first_name`                  varchar(100),
  `last_name`                   varchar(100),
  `title`                       varchar(25),
  `department`                  varchar(255),
  `birthdate`                   date,
  `do_not_call`                 char(3) DEFAULT '0',
  `phone_home`                  varchar(25),
  `phone_mobile`                varchar(25),
  `phone_work`                  varchar(25),
  `phone_other`                 varchar(25),
  `phone_fax`                   varchar(25),
  `email1`                      varchar(100),
  `email2`                      varchar(100),
  `assistant`                   varchar(75),
  `assistant_phone`             varchar(25),
  `email_opt_out`               char(3) DEFAULT '0',
  `primary_address_street`      varchar(150),
  `primary_address_city`        varchar(100),
  `primary_address_state`       varchar(100),
  `primary_address_postalcode`  varchar(20),
  `primary_address_country`     varchar(100),
  `alt_address_street`          varchar(150),
  `alt_address_city`            varchar(100),
  `alt_address_state`           varchar(100),
  `alt_address_postalcode`      varchar(20),
  `alt_address_country`         varchar(100),
  `description`                 longtext,
  `invalid_email`               tinyint(1) DEFAULT '0',
  `lead_id`                     varchar(36),
  `account_name`                varchar(150),
  /* Keys */
  PRIMARY KEY (`id`)
) 
TYPE = MyISAM;

CREATE INDEX `idx_prospecs_del_last`
  ON `prospects`
  (`last_name`, `deleted`);

CREATE INDEX `idx_prospects_last_first`
  ON `prospects`
  (`last_name`, `first_name`, `deleted`);

CREATE INDEX `prospect_auto_tracker_key`
  ON `prospects`
  (`tracker_key`);

TlcAndres · 02-06-2008, 10:57 AM

If I'm not mistaken, the field that is to be incremented has to be the primary key

Orc · 02-06-2008, 11:06 AM

Hell yeah it has to be a primary key, that's probably the only reason, there are two ways to do this:

1.

sql Code:

CREATE TABLE `blah` (
       `id` int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY
);

2. [highlight=sql]
CREATE TABLE `bleh2` (
`id` int(5) not null auto_increment,
KEY `id` ( `id` ) // OR this
PRIMARY KEY (`id`)
);
[/hightlight]

Your choice, they all work the same way.

Edit: What the crap?