1

Iam trying to make the "in_reply_to" field in the Post table a nullable foreign key that refers to the id field on the same table. Basically so if this field is null, then the post is not a reply to another post – if it is non-null then the post is a reply to the post with the matching id if that makes any sense. But when i run my file through mysql i get the following error.

ERROR 1064 (42000): 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 '(160) , FOREIGN KEY (userID) REFERENCES users(userID), FOREIGN KEY (in_reply_' at line 6 Query OK, 0 rows affected (0.36 sec)

So it is saying something is wrong with the commands i have written, but i cant pinpoint what iam doing wrong.

Here is the contents of the sql file

CREATE DATABASE siteData CHARACTER SET utf8 COLLATE utf8_general_ci;

 USE siteData;

 CREATE TABLE users (
userID INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(40),
email VARCHAR(40),
password VARCHAR(125)
);

 CREATE TABLE post (
postID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
userID INT,
post_date TIMESTAMP(6),
in_reply_to INT NOT NULL,
postContent(160),
    FOREIGN KEY (userID) REFERENCES users(userID),
    FOREIGN KEY (in_reply_to) REFERENCES post(postID)   
); 

 CREATE TABLE follow (
userID INT,
followerID INT,
PRIMARY KEY (userID,followerID),

FOREIGN KEY (userID)
        REFERENCES users(userID),
FOREIGN KEY (followerID)
        REFERENCES users(userID)
);

So if you have any pointers on how to make it reference the id of post, i would appreciate it.

MrKeyboard
  • 107
  • 9
  • The error is `postContent(160),`, I would guess it should be `postContent VARCHAR(160),`. Your actual foreign keys are fine. Although I would suggest you want `in_reply_to ` to be not null -- `in_reply_to INT NULL,` rather than `in_reply_to INT NOT NULL,` – GarethD Sep 03 '15 at 11:07
  • Thankyou, i cant believe it was something so stupid that i forgot. – MrKeyboard Sep 03 '15 at 11:10
  • thankyou for your help and tips, i ran it and no errors :) – MrKeyboard Sep 03 '15 at 11:11

2 Answers2

2

The problem is not the foreign key. You haven't declared the type for column postContent.

Please change

postContent(160),

to e.g.

postContent varchar(160),

If you want your column in_reply_to to be nullable, please change 

in_reply_to INT NOT NULL,

to

in_reply_to INT,
mario.van.zadel
  • 2,919
  • 14
  • 23
1

There is a missing Datatype of field postContent in Post Table. But this is not the right way to make foreign key null. Still there is a trick to do that. You can check the following link.

Foreign keys and NULL in mySQL

Community
  • 1
  • 1
Sumit
  • 31
  • 7