Create table for newsletter problem

[norvica]

Create table for newsletter problem

I am trying to create tables for a newsletter management system which has

1. Newsletter categories
2. A number of newsletters in each category
3. A list of subscribers

and am getting the following error:

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

for sql2... the subscribers table.

Any idea as to how to rewrite these tables?

Thanks!

$sql0 = ("create table if not exists newslettercategories ( 
newslettercategoryid int(4) default '0' auto_increment not null, 
newslettercategoryname varchar(100) not null,
newslettercategorycreatetime varchar(30) not null, 
newslettercategoryupdatetime varchar(30) not null, 
primary key(newslettercategoryid)
);"); 

$sql1 = ("create table if not exists newsletters ( 
newsletterid int(4) default '0' auto_increment not null, 
newslettercategoryid int(4) default '0' not null, 
newslettername varchar(100) not null, 
newslettersubject varchar(200) not null, 
newslettercontent text not null, 
newslettercreatetime varchar(30) not null, 
newsletterupdatetime varchar(30) not null, 
primary key(newsletterid)
);"); 

$sql2 = ("create table if not exists subscribers ( 
subscriberid int(12) default '0' auto_increment not null, 
newsletterid int(8)  default '0' not null, 
newslettercategoryid int(8)  default '0' not null, 
email varchar(50) not null,
subscribersignuptime varchar(30) not null, 
subscriberupdatetime varchar(30) not null, 
subscriberipaddress varchar(30) not null, 
primary key (email, newslettercategoryid)
);");

 

[gesf]

Seems that 'email' can´t be your primary key! It must be subscriberid.

Try this out (in sql2):

$sql2 = ("CREATE TABLE IF NOT EXISTS subscribers ( 
subscriberid int(12) default '0' auto_increment not null, 
newsletterid int(8)  default '0' not null, 
newslettercategoryid int(8)  default '0' not null, 
email varchar(50) not null, 
subscribersignuptime varchar(30) not null, 
subscriberupdatetime varchar(30) not null, 
subscriberipaddress varchar(30) not null, 
PRIMARY KEY (subscriberid), KEY(newslettercategoryid)
);");