Strange Error When Creating Table!

I'm trying to create my first database. The problem is, I get a strange error when I try to create my last table (Coupon_Table). It's the only one with foreign keys, which I think are causing the error. What am I doing wrong? Please advise.

Database

Advertiser_Table

advertiser_id
advertiser_name
advertiser_logo
advertiser_url

-------------

Offer_Instructions_Table

offer_instructions_id
offer_instructions
block_anchor_text

-------------

Category_Table

category_id
category_name
category_url

-------------

Coupon_Table

coupon_id
offer_anchor_text
offer_url
coupon_code
offer_expiration_date
advertiser_id
offer_instructions_id
category_id

Code

create table Advertiser_Table (
advertiser_id smallint unsigned not null auto_increment primary key,
advertiser_name char(25),
advertiser_logo char(25),
advertiser_url char(30)
) engine=innodb;


create table Offer_Instructions_Table (
offer_instructions_id smallint unsigned not null auto_increment primary key,
offer_instructions char(45),
block_anchor_text char(10)
) engine=innodb;


create table Category_Table (
category_id smallint unsigned not null auto_increment primary key,
category_name char(20),
category_url char(36)
) engine=innodb;


create table Coupon_Table (
coupon_id smallint unsigned not null auto_increment primary key,
offer_anchor_text char(40),
offer_url varchar(200),
coupon_code char(25),
offer_expiration_date date,
foreign key (advertiser_id) references Advertiser_Table (advertiser_id),
foreign key (offer_instructions_id) references Offer_Instructions_Table (offer_instructions_id),
foreign key (category_id) references Category_Table (category_id)
) engine=innodb;

ERROR 1072 (42000): Key column 'advertiser_id' doesn't exist in table

 

I would say you need to actually define advertiser_id etc as columns in Coupon_Table, and then define the foreign keys on them.

 

Hi, Wootty,

Do you mean create Coupon_Table first, then the other (parent) tables? If so, I've already tried that. I still get the same error.

Any other ideas?

 

Oh . . . Now I get it!

I should define the columns' attributes (smallint, etc.), before assigning them as foreign keys.

Thanks, Wootty!

I tried to give you reputation points again but the system wouldn't let me. Apparently, I have to spread some points around before giving more to you. Sorry, dude.

 

create table Coupon_Table (
coupon_id smallint unsigned not null auto_increment primary key,
offer_anchor_text char(40),
offer_url varchar(200),
coupon_code char(25),
offer_expiration_date date,
foreign key (advertiser_id) references Advertiser_Table (advertiser_id),
foreign key (offer_instructions_id) references Offer_Instructions_Table (offer_instructions_id),
foreign key (category_id) references Category_Table (category_id)
) engine=innodb;

You need a field called advertiser_id in this table too.
For example:

create table Coupon_Table (
coupon_id smallint unsigned not null auto_increment primary key,
offer_anchor_text char(40),
offer_url varchar(200),
coupon_code char(25),
offer_expiration_date date,
advertiser_id smallint unsigned not null
foreign key (advertiser_id) references Advertiser_Table (advertiser_id),
foreign key (offer_instructions_id) references Offer_Instructions_Table (offer_instructions_id),
foreign key (category_id) references Category_Table (category_id)
) engine=innodb;

It should work now.