I dont know whether the table design is correct, as i am very beginner in this. Also this design causing me many errors.
Code:
CREATE TABLE message_index(
id INT NOT NULL AUTO_INCREMENT,
message_from INT NOT NULL,
message_to INT NOT NULL,
message_subject VARCHAR(100) NOT NULL,
message_body VARCHAR(1000) NOT NULL,
message_datetime DATETIME NOT NULL,
onfrom ENUM('0','1','2') NOT NULL,
onto ENUM('0','1','2') NOT NULL,
INDEX _id(id),
PRIMARY KEY(id))ENGINE=INNODB;
Code:
CREATE TABLE messages(
msgid INT NOT NULL,
message_from INT NOT NULL,
message_to INT NOT NULL,
msg_body VARCHAR(1000) NOT NULL,
msg_datetime DATETIME NOT NULL,
INDEX msg_id(msgid),
FOREIGN KEY(msgid) REFERENCES message_index(id) ON UPDATE CASCADE ON DELETE CASCADE)ENGINE = INNODB;