Date: 08/09/05 (MySQL Communtiy)    Keywords: browser, database

it keeps throwing errors



/*********

postings & comments database structure
written by : that intern

*********/

CREATE DATABASE if not exists commentsDB;
USE commentsDB;

DROP TABLE IF EXISTS `userDB`; /* works fine! */
CREATE TABLE `userDB` (
`userID` INT NOT NULL AUTO_INCREMENT,
`username` varchar(8) NOT NULL,
`userpass` VARCHAR(8) NOT NULL,
`first_name` VARCHAR(10) NULL,
`last_name` VARCHAR(15) NULL,
`address_1` VARCHAR(25) NULL,
`address_2` VARCHAR(15) NULL,
`city` VARCHAR(18) NULL,
`state` VARCHAR(2) NULL,
`zip_code` TEXT NOT NULL,
`email` VARCHAR(25) NOT NULL,
`gender` VARCHAR(1) NULL,
PRIMARY KEY (`userID`)
) TYPE=InnoDB;

DROP TABLE IF EXISTS `userInfo`;
CREATE TABLE userInfo (
`browser` VARCHAR(255) NULL,
`userIP` TEXT NULL,
`userOS` varchar(255) default NULL,
`pages_visited` varchar(255) default NOT NULL,
`last_login` varchar(255) default NOT NULL, /* works until here */
PRIMARY KEY (`userID`),
FOREIGN KEY (`userID`) FROM userDB(`userID`)
) TYPE=InnoDB;

DROP TABLE IF EXISTS `post`; /* works fine! */
CREATE TABLE `post` (
`post_num` INT(15) NOT NULL,
`post_text` TEXT NOT NULL,
`post_cat` VARCHAR(10) NOT NULL,
`post_time` TIMESTAMP NOT NULL,
`post_date` DATE NOT NULL,
`post_user` VARCHAR(15) NOT NULL,
PRIMARY KEY (`post_num`)
) TYPE=InnoDB;

DROP TABLE IF EXISTS `comments`;
CREATE TABLE `comments` (
`comment_text` TEXT NULL,
`comment_time` TIMESTAMP NOT NULL,
`comment_date` DATE NOT NULL,
`userIP` TEXT NOT NULL /* works until here */
post_num INT,
userID INT,
FOREIGN KEY (post_num) REFERENCES post(post_num),
FOREIGN KEY (userID) REFERENCES userDB(userID)
) TYPE=InnoDB;

Source: http://www.livejournal.com/community/mysql/66303.html