From: Scott Purcell (spurcellvertisinc.com)
Date: Fri Jul 01 2005 - 14:46:49 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]