Jannie, In CREATE TABLE ... TYPE=InnoDB with a foreign key ref, MySQL seems to want a type def and NOT NULL in the definition of the column to be used as a foreign key.
PB ----- ----- Original Message ----- From: "Jannie Qu" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, October 29, 2002 10:18 AM Subject: Create child table question > sql, query, mysql > > Hi, all, > I met a problem with creating the following table: pub_user. > -- Parent table > CREATE TABLE pub_access_type( > access_type_id INT UNSIGNED NOT NULL, > access_type_name VARCHAR(25), > PRIMARY KEY(access_type_id) > ) TYPE=INNODB; > -- Child table > CREATE TABLE pub_user( > user_id INT UNSIGNED, > parent_id INT, > user_fname VARCHAR (25), > user_lname VARCHAR (25), > user_password VARCHAR (25), > INDEX par_ind(parent_id), > FOREIGN KEY pub_user(parent_id) REFERENCES pub_access_type(access_type_id) > ON DELETE SET NULL > ) TYPE=INNODB; > > If I use "FOREIGN KEY pub_user(parent_id) REFERENCES pub_access_type.." > Table "pub_user" will be created, but if I use "FOREIGN KEY (parent_id) > REFERENCES pub_access_type" I will get the following error: > ERROR 1005: Can't create table '.\staging\pub_user.frm' (errno: 150) > > I found a similar script which don't use tablename when "foreign key (column > name...) > Here it is: > CREATE TABLE parent1 > (id INT NOT NULL, > name varchar(10), > PRIMARY KEY (id) > ) TYPE=INNODB; > CREATE TABLE child1 > (id INT, name varchar(10), parent_id INT, tellphone varchar(10), > INDEX par_ind(parent_id), > FOREIGN KEY (parent_id) REFERENCES parent1(id) > ON DELETE SET NULL > ) TYPE=INNODB; > > > I wonder why my scripts must specify tablename? > > Thank you, > Jing > > > > > > _________________________________________________________________ > Choose an Internet access plan right for you -- try MSN! > http://resourcecenter.msn.com/access/plans/default.asp > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php