My SQL Database Creation

[Axehack]

Hi, for some reason my tables wont create... i cant see anything wrong with the code tho, have a look.

create table projpromo
(
promoid int(10) auto_increment,
promotype char(20),
promotitle char(30),
promodate DATE,
promotime int(5),
promodesc TEXT,
primary key (promoid,promotype)
);


create table projuser
(
userid int(10) auto_increment,
promotype char(20),
title char(10),
forename char(20),
surname char(20),
email char(20),
acctyp char(10),
accpass char(20),
primary key (userid),
foreign key (promotype) references projpromo(promotype)
);


Please let me know asap if u realise what im doing wrong.

Cheerz

 

[strollin]

What error message do you get? Also, what db are you using? Different dbs may use different names for their data types.

 

[Baez]

You'll need to post the errors.

For the second you need an engine that supports foreign keys. Engines such as MyISAM and MEMORY don't have support for them.

 

[Axehack]

I am using mySQL
The error code is: 150 (foreign key restraint i believe, but I dont see the problem)

The 1st table gets created, but nothing does after that. I had it configured so that the second table referenced promoID rather than type have discovered that this will not work.

I am considering making another table for promotype, as this may be a better idea

 

[Baez]

Then did you read the second part of my post? It's very likely that you're trying to apply a foreign key to a table using MyISAM.

 

[Axehack]

no thats not the case, as every database we have created for the past 2 years have contained foreign keys.
After a bit of thought, i realised that I had to create an additional table anyway for proper reference. All is working well now! Thanks for your replies!