"Scott Purcell" <[EMAIL PROTECTED]> wrote on 07/01/2005 03:46:49 PM:
> Hello,
>
>
> MYSQL on PC v 4.0.15
>
> And I am trying to add an address table off of it. Using the
> users(id) as a primary key and deleting on cascade. So when the user
> is deleted from system, so are the addresses associted with him.
>
> // code that errors
> CREATE TABLE `address` (
> `id` int(11),
> `created_date` timestamp(14) NOT NULL,
> `firstname` varchar(25) default NULL,
> `initial` char(1) default NULL,
> `lastname` varchar(25) default NULL,
> `address1` varchar(50) default NULL,
> `address2` varchar(50) default NULL,
> `city` varchar(50) default NULL,
> `state` char(2) default NULL,
> `zip` varchar(5) default NULL,
> `phone` varchar(12) default NULL,
> `eveningPhone` varchar(12) default NULL,
> KEY(id),
> foreign key(id) references user(id)
> on DELETE CASCADE
> ) TYPE=InnoDB;
>
>
> message:
> Error Code : 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 'user(id),
> on DELETE CASCADE
> ) TYPE=InnoDB' at line 15
> (0 ms taken)
>
>
> here is my original user table:
>
> CREATE TABLE `user` (
> `id` int(11) NOT NULL auto_increment,
> `modified_date` timestamp(14) NOT NULL,
> `created_date` timestamp(14) NOT NULL,
> `username` varchar(50) NOT NULL default '',
> `firstname` varchar(25) default NULL,
> `initial` char(1) default NULL,
> `lastname` varchar(25) default NULL,
> `company` varchar(50) default NULL,
> `address1` varchar(50) default NULL,
> `address2` varchar(50) default NULL,
> `city` varchar(50) default NULL,
> `state` char(2) default NULL,
> `zip` varchar(5) default NULL,
> `phone` varchar(12) default NULL,
> `eveningPhone` varchar(12) default NULL,
> `email` varchar(50) default NULL,
> `password` varchar(50) default NULL,
> `admin` char(1) NOT NULL default 'F',
> `hintchoice` char(1) default NULL,
> `hintvalue` varchar(50) default NULL,
> PRIMARY KEY (`id`)
> ) TYPE=MyISAM;
>
> I have a lot of data here, so I don't want to lose any.
>
> Thanks,
> Scott
>
You are missing a space:
`eveningPhone` varchar(12) default NULL,
KEY(id),
foreign key(id) references user (id) on DELETE CASCADE
^-----space goes here
) TYPE=InnoDB;
without the space, the engine thinks you want to use the *function* USER()
in that location.
http://dev.mysql.com/doc/mysql/en/information-functions.html
Something else that will help avoid this type of error in the future is to
surround your table and column names with backticks just as you did when
you defined your columns....
`eveningPhone` varchar(12) default NULL,
KEY(`id`),
foreign key(`id`) references `user` (`id`) on DELETE CASCADE
) TYPE=InnoDB;
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
