Assigning foreign key

hello folks,
i am developing a simple blog using PHP & MYSQL. i complete the database design and next stage is to create the tables. upon creating the tables i encounter an error i tried and tried many times to get it right , search on Google online still no success. i kindly ask anyone for assistance please.

the purpose of the code is to create two tables: Post and comments

the Post table will contain all the posts made by the author and comments table will contain all the comments made on each individual post made by users. The constraint here is that all comments must be associated with all post respectively. therefore i have to assign an foreign key on the comments table to relate to the post table.

this is the error am getting:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘CREATE TABLE comment ( comment_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY K’ at line 8



CREATE TABLE post (
post_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100) NOT NULL,
post TEXT NOT NULL,
date_entered DATETIME NOT NULL
);

CREATE TABLE comment (
comment_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
comment TEXT NOT NULL,
comment_date  DATETIME NOT NULL,
email VARCHAR(50), 
post_comments_id INT NOT NULL,
FOREIGN KEY (post_comments_id) REFERENCES post(post_id)
);


Comment is a reserved word so you need to choose a different table name or “quote” the name with backticks(comment).

are you running these through PHP? Are you using mysql(i)_query as opposed to [FPHP]mysqli_multi_query[/FPHP]?

Comment is not a mysql reserved word. http://dev.mysql.com/doc/refman/5.6/en/reserved-words.html

i am using SQL BUDDY to run the queries.

Which is using PHP…if you run each query individually, do you get the same error?

yea i tried to run each query separate but still nothing. i have start to use php yet creating the table through sql buddy i tried it in phpmyadmin still the same thing

Well INT requires a size definition, IIRC…Other than that I dont see anything glaringly wrong… i’m gonna flag this post for moving into the MySQL forum, cause it’ll grab the attention of those who might see something i’m missing.

oh i now realize i post it in the wrong section sorry about that didnt mean it. thank you for helping.

nope, it doesn’t, and it’s best just to use INTEGER without the size

INTEGER(1) is the same as INTEGER(12) is the same as INTEGER(937) as far as how “big” the column is, i.e. the range of numbers it can hold is always the same

as far as the problem in this thread, there is nothing wrong with the CREATE TABLE statement that creates the comment table

is there another way I could write this statement. because when i delete the foreign key reference line the query work.

your foreign key is INT but the primary key it references is INT UNSIGNED – they have to be the same

oh thk you very much

you are right it work.thanks