When I run query below on phpMyAdmin and Sequel, I got the following error
#1064 - 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 'id Int UNSIGNED NOT NULL AUTO_INCREMENT, login Char(128) NOT NULL, passw' at line 3.
I can't figure out the error on this query. Mysql is running on 5.5.25 version.
CREATE TABLE user
(
id Int UNSIGNED NOT NULL AUTO_INCREMENT,
login Char(128) NOT NULL,
password Char(128) NOT NULL,
emailaddress Char(128),
alternateemailaddress Char(128),
officephone Char(20),
officefax Char(20),
mobilephone Char(20),
client Int UNSIGNED,
facility Int UNSIGNED,
user_status Int UNSIGNED NOT NULL DEFAULT 0,
valid_from Datetime NOT NULL,
valid_to Datetime NOT NULL,
last_login Datetime,
last_login_from Char(48),
modified_by Int NOT NULL,
modified_time Datetime,
PRIMARY KEY (id),
FOREIGN KEY client REFERENCES client (id) ON DELETE CASCADE,
FOREIGN KEY facility REFERENCES facility (id) ON DELETE CASCADE
) ENGINE = InnoDB
AUTO_INCREMENT = 0
;
You should use backsticks when writing names of tables and fields to avoid MySQL parse it as reserved keywords. You should also use parentheses wrapping your foreign keys names. Working query should look like this:
CREATE TABLE `user`
(
`id` Int UNSIGNED NOT NULL AUTO_INCREMENT,
`login` Char(128) NOT NULL,
`password` Char(128) NOT NULL,
`emailaddress` Char(128),
`alternateemailaddress` Char(128),
`officephone` Char(20),
`officefax` Char(20),
`mobilephone` Char(20),
`client` Int UNSIGNED,
`facility` Int UNSIGNED,
`user_status` Int UNSIGNED NOT NULL DEFAULT 0,
`valid_from` Datetime NOT NULL,
`valid_to` Datetime NOT NULL,
`last_login` Datetime,
`last_login_from` Char(48),
`modified_by` Int NOT NULL,
`modified_time` Datetime,
PRIMARY KEY (`id`),
FOREIGN KEY (`client`) REFERENCES `client` (`id`) ON DELETE CASCADE,
FOREIGN KEY (`facility`) REFERENCES `facility` (`id`) ON DELETE CASCADE
) ENGINE = InnoDB
AUTO_INCREMENT = 0
;