Fix that by, say, defining a primary key on t1, and the foreign key constraint will be created successfully. SHOW INDEX FROM t1 shows that there aren’t any indexes at all for table t1. It says that the problem is it can’t find an index. In the table and the referenced table do not match for constraint. Referenced columns appear as the first columns, or column types But run SHOW ENGINE INNODB STATUS and it will say: -ġ30811 23:36:38 Error in foreign key constraint of table test/t2:Ĭannot find an index in the referenced table where the That doesn’t tell anyone anything useful other than that it’s a foreign key problem. You can get the actual error message by running SHOW ENGINE INNODB STATUS and then looking for LATEST FOREIGN KEY ERROR in the output.įor example, this attempt to create a foreign key constraint: CREATE TABLE t1ĬONSTRAINT FOREIGN KEY (t1_id) REFERENCES t1 (id)) įails with the error Can't create table 'test.t2' (errno: 150). MySQL’s generic “errno 150” message “ means that a foreign key constraint was not correctly formed.” As you probably already know if you are reading this page, the generic “errno: 150” error message is really unhelpful. See answer below for instructions on how to identify these problem FK's. This may be a result of past changes where SET FOREIGN_KEY_CHECKS = 0 was utilized with an inconsistent relationship defined by mistake. different collation), they will need to be made consistent first. If there are any other FK's in other tables pointing at the same field you are attempting to create the new FK for, and they are malformed (i.e. Two tables may not have their own constraint with the same name. If you declare a constraint name for a foreign key, the constraint name must be unique in the whole schema, not only in the table in which the constraint is defined. If you declare a FK with the ON DELETE SET NULL option, then the FK column(s) must be nullable. Neither the Parent table nor the Child table can be a PARTITIONED table. Neither the Parent table nor the Child table can be a TEMPORARY table. Obviously, this query is an generic example you must substitute your table names and column names. This must return zero (0) unmatched values. Check this with a query like: SELECT COUNT(*) FROM Child LEFT OUTER JOIN Parent ON Child.FK = Parent.PK If there is data already in the Child table, every value in the FK column(s) must match a value in the Parent table PK column(s). For example, VARCHAR(10) can reference VARCHAR(20) or vice versa.Īny string-type FK column(s) must have the same character set and collation as the corresponding PK column(s). For example, if a PK column in the Parent table is UNSIGNED, be sure to define UNSIGNED for the corresponding column in the Child table field.Įxception: length of strings may be different. The PK column(s) in the Parent table must be the same data type as the FK column(s) in the Child table. For example, if the FK REFERENCES Parent(a,b,c) then the Parent's PK must not be defined on columns in order (a,c,b). The FK definition must reference the PK column(s) in the same order as the PK definition. Best if the key in the Parent is PRIMARY KEY or UNIQUE KEY. The referenced columns in the Parent table must be the left-most columns of a key. Other storage engines silently ignore foreign key definitions, so they return no error or warning, but the FK constraint is not saved. The two tables must both support foreign key constraints, i.e. If both tables references each other, you must create one table without FK constraints, then create the second table, then add the FK constraint to the first table with ALTER TABLE. You must define the tables in the right order: Parent table first, then the Child table. The Parent table must exist before you define a foreign key to reference it. These conditions must be satisfied to not get error 150 re ALTER TABLE ADD FOREIGN KEY:
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |