Bug #24369 | can't create new table with Foreign Keys referencing a current table with data | ||
---|---|---|---|
Submitted: | 16 Nov 2006 17:06 | Modified: | 20 Nov 2006 23:40 |
Reporter: | kevin martin | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S1 (Critical) |
Version: | 5.0.28 | OS: | Linux (Linux and Solaris) |
Assigned to: | Heikki Tuuri | CPU Architecture: | Any |
[16 Nov 2006 17:06]
kevin martin
[16 Nov 2006 23:27]
Hartmut Holzgraefe
could you provide a working self contained SQL example (both CREATE statements and some sample INSERTs)?
[20 Nov 2006 15:02]
Heikki Tuuri
Kevin, the problem is that you have declared the guid type in one table as BINARY and in the other table not. The columns must have compatible types in the parent table and the child table. " heikki@127:~/mysql-5.0/client$ ./mysql -uroot test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.29-debug Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> create table alerts (alert_guid varchar(32) primary key) engine = InnoDB; Query OK, 0 rows affected (0.08 sec) mysql> create table alert_fact -> ( -> alert_guid CHAR(32) BINARY not null, -> fact_id CHAR(32) BINARY not null, -> -> fact_vendor VARCHAR(64) BINARY not null, -> fact_bundle VARCHAR(32) BINARY not null, -> fact_version VARCHAR(32) BINARY not null, -> fact_classname VARCHAR(64) BINARY not null, -> -> fact_value LONGBLOB, -> -> PRIMARY KEY(alert_guid, fact_id), -> FOREIGN KEY(alert_guid) REFERENCES alerts(alert_guid), -> INDEX (alert_guid(8)) -> ) TYPE = InnoDB; ERROR 1005 (HY000): Can't create table './test/alert_fact.frm' (errno: 150) mysql> mysql> show InnoDB status\G *************************** 1. row *************************** Status: ===================================== 061120 17:01:38 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 60 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 3, signal count 3 Mutex spin waits 0, rounds 0, OS waits 0 RW-shared spins 6, OS waits 3; RW-excl spins 0, OS waits 0 ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 061120 17:01:28 Error in foreign key constraint of table test/alert_fact: FOREIGN KEY(alert_guid) REFERENCES alerts(alert_guid), INDEX (alert_guid(8)) ) TYPE = InnoDB: Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html for correct foreign key definition. ... " You can print a detailed account of the latest foreign key error with SHOW INNODB STATUS\G. Regards, Heikki
[20 Nov 2006 23:40]
kevin martin
Yep, you're right. The problem was that I could not figure out how to tell what the referenced field was built like. I ultimately just dumped the schema and data for the database, dropped the database, added the new table to the schema, then rebuilt the database. Little more work than I had hoped but it allowed me to do what I needed.