Jannie, ----- 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? in InnoDB you cannot give an index or table name immediately after 'FOREIGN KEY' This is wrong syntax: "FOREIGN KEY pub_user(parent_id) REFERENCES pub_access_type(access_type_id)" InnoDB ignores such foreign key definition. I will change it so that it gives an error 150. This is the right syntax: "FOREIGN KEY (parent_id) REFERENCES pub_access_type(access_type_id)" You get error 150 in this case because in the parent table the column is INT UNSIGNED, but in the child just INT. > Thank you, > Jing Regards, Heikki Innobase Oy sql query --------------------------------------------------------------------- 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
