Error when creating tables with foreign keys

[3D-kreativ]

Hi!

 

I get this error: Error code: 1005 (Can't create table 'blogg.pega09bloguser' (errno: 150)) After a lot of searching on the Internet I have tried to change change between InnoDB and MyISAM, but this doesn't work. I use MySQL Workbench 5.2.25. I don't know what to do, so I preciate some help so that I can continue. Thanks!

 

-- Table for the user
--
CREATE TABLE {$tableUser} (

userId INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY,
userNameFirst VARCHAR(15) NOT NULL,
userNameLast VARCHAR(15) NOT NULL,
userEmail VARCHAR(35) NOT NULL,
userInfo TINYTEXT,
userPassword VARCHAR(32) NOT NULL  
                 
);

-- Table for the posts
--
CREATE TABLE {$tablePost} (

postId INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY,
postUserId INT(11),
FOREIGN KEY(postUserId) REFERENCE {$tableUser} (userId),
postSubject VARCHAR(50) NOT NULL,
postText TEXT NOT NULL,
postDate TIMESTAMP NOT NULL DEFAULT NOW() 

);

-- Table for the comments
CREATE TABLE {$tableComment} (

commentId INT(11) AUTO_INCREMENT NOT NULL PRIMARY KEY,
commentPostId INT(11),
FOREIGN KEY(commentPostId) REFERENCE {$tablePost} (postId),
commentPost TEXT NOT NULL,
commentSignature VARCHAR(30),
commentDate TIMESTAMP NOT NULL DEFAULT NOW()

);

[bh]

This is wrong:

postDate TIMESTAMP NOT NULL DEFAULT NOW()

 

->

 

Its from MySQL manual:

The default can be CURRENT_TIMESTAMP or a constant date and time value.

[3D-kreativ]

Thanks! I have changed that now, but the main problem is still there and I don't know how to find the error?

[3D-kreativ]

Hi, I discovered that I had made a mistake spelling REFERENCE, I forgot the S in the end. But the problem seems also be about some bug when using a multi_query.

 

But I get this error when I whant to run the script each time and drop existing tables. I don't get the error if I erase the tables in right order in MySQL Workbench. What could be the problem? Thanks!

 

ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails

 

 

 

// Prepare and perform a SQL query.
//
$tableUser = DB_PREFIX . 'blogUser';
$tablePost	= DB_PREFIX . 'blogPost';
$tableComment = DB_PREFIX . 'blogComment';


$queryArray = array(

"DROP TABLE IF EXISTS {$tableUser};",
"DROP TABLE IF EXISTS {$tablePost};",
"DROP TABLE IF EXISTS {$tableComment};",

[Pikachu2000]

Might have to DROP INDEX before being able to DROP TABLE.