Create child table question

[Jannie Qu]

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