Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Create table for newsletter problem 1

Status
Not open for further replies.

mancroft

Programmer
Joined
Oct 26, 2002
Messages
267
Location
GB
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!

Code:
$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)
);");
 
This sounds to me like a MySQL question, not a PHP question.


The error message is very explicit.

You're setting the "auto_increment" property on the column "subscriberid", but you're not setting that column as a key or part of a key.



Want the best answers? Ask the best questions!

TANSTAAFL!!
 
ADDENDUM:

This from the MySQL online manual entry for CREATE TABLE:

Note: There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value.



Want the best answers? Ask the best questions!

TANSTAAFL!!
 
Thank yoy for the pointer, sleipnir214.

I got the following to work.

Code:
$sql0 = ("create table if not exists newslettercategories ( 
newslettercategoryid int(4)  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)  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)  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, email, newslettercategoryid)
);");
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top