mysql foreign key problem

Question

What is wrong with the foreign key addition here:

mysql> create table notes ( 
     id int (11) NOT NULL auto_increment PRIMARY KEY, 
     note_type_id smallint(5) NOT NULL, 
     data TEXT NOT NULL, 
     created_date datetime NOT NULL, 
     modified_date timestamp NOT NULL on update now()) 
     Engine=InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql> create table notetypes ( 
       id smallint (5) NOT NULL auto_increment PRIMARY KEY, 
       type varchar(255) NOT NULL UNIQUE) 
       Engine=InnoDB;
Query OK, 0 rows affected (0.00 sec)


mysql> alter table `notes` add constraint 
      foreign key(`note_type_id`) references `notetypes`.`id` 
      on update cascade on delete restrict;
ERROR 1005 (HY000): Can't create table './admin/#sql-43e_b762.frm' (errno: 150)

Thanks

JP

Can you disable the FK before creation, and re-enable it afterwards? `set foreign_key_checks=0;` ... — Konerak, Jan 02 '11 at 13:42
Dropped both tables. Did `set foreign_key_checks=0;` and then created the tables, then added foreign keys. But still same error. — , Jan 02 '11 at 13:44
Hmm. What does `SHOW INNODB STATUS;` show under foreign key error? — Konerak, Jan 02 '11 at 14:59
Does http://stackoverflow.com/questions/160233/what-does-mysql-error-1025-hy000-error-on-rename-of-foo-errorno-150-mean help? — Konerak, Jan 02 '11 at 15:04

score 1 · Answer 1 · answered Jan 02 '11 at 14:34

1

I think the FK needs to be named.

Try

ALTER TABLE `test`.`notes` ADD CONSTRAINT `note_type_id` FOREIGN KEY `note_type_id` (`note_type_id`)
    REFERENCES `notetypes` (`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE;

answered Jan 02 '11 at 14:34

bob

31
1
5

same error. I think mysql internally names anonymous keys.. i have used anonymous keys/contraints in the past. – Jan 02 '11 at 14:44
very strange... I ran your three queries and received the same error on index creation. Running the above query worked fine for me - very odd... – bob Jan 02 '11 at 14:46
Is it? Let me try again - I might have missed something in what you wrote above. – Jan 03 '11 at 04:11
Oops. Your thing does work, but I was using wrong syntax `notetypes`.`id` instead of `note_type_id` (`note_type_id`). (not naming problem). Thanks though ! +1. – Jan 03 '11 at 16:54

score 0 · Accepted Answer · answered Jan 03 '11 at 11:21

mysql> create table notes ( id int (11) NOT NULL auto_increment PRIMARY KEY, note_type_id smallint(5) NOT NULL, data TEXT NOT NULL, created_date datetime NOT NULL, modified_date timestamp NOT NULL on update now()) Engine=InnoDB;
Query OK, 0 rows affected (0.38 sec)

mysql> create table notetypes ( id smallint (5) NOT NULL auto_increment PRIMARY KEY, type varchar(255) NOT NULL UNIQUE) Engine=InnoDB;
Query OK, 0 rows affected (0.74 sec)

mysql> alter table `notes` add constraint foreign key(`note_type_id`) references notetypes (id)  on update cascade on delete restrict;
Query OK, 0 rows affected (0.64 sec)
Records: 0  Duplicates: 0  Warnings: 0

Thanks! it works... So mysql gives some crazy error instead of syntax error in this case. — , Jan 03 '11 at 16:55

mysql foreign key problem

2 Answers2