See §§ 14.2.2.5 "FOREIGN KEY Constraints" and 13.1.17.2 "Using FOREIGN KEY Constraints" in the MySQL 5.6 Reference Manual. That means the Foreign Key constraint in MySQL is used for binding two tables with each other and then verify the existence of one table data in other tables. But InnoDB doesn't support deferred checks, so it treats ON DELETE NO ACTION exactly the same as ON DELETE RESTRICT, and always rejects the DELETE immediately.) (Note: in some DBMSes, and in standard SQL, ON DELETE NO ACTION is a bit different from ON DELETE RESTRICT: in those, ON DELETE NO ACTION means "accept the DELETE within the current transaction, but reject the whole transaction if I try to commit it before rectifying the problem". So InnoDB can't fix the situation for you all it can do is reject the DELETE and return an error.Īs a result, ON DELETE NO ACTION is actually the same as ON DELETE RESTRICT (the default). If you specify NO ACTION, you're telling InnoDB that you don't want it to take either of these actions. If you hard-delete a user, you might want the file's last-modified-by to become simply "unknown".) (This might make sense for something like file.last_modified_by. it can SET NULL, meaning, clear out the referring key.If you hard-delete a user, you probably want to hard-delete all of the user's addresses as well.) (This would make sense for something like user_er_id. it can CASCADE, meaning, delete the referring record.When you delete a record that is referred to by a foreign key, InnoDB has the ability to take an automatic action to rectify the situation: It does not mean to suppress the foreign-key constraint. Note: If the table is not of InnoDB, Soliddb or PBXT types, SQLyog will give an error…You can change the handler of table by selecting it from the Table Engine combo box.I think you're misunderstanding what ON DELETE NO ACTION means. Second, specify a list of comma-separated foreign key columns after the FOREIGN KEY keywords. If you omit the constraint name, MySQL automatically generates a name for the foreign key constraint. Select the Child table in the Object Browser and press F10 or select Relationships/Foreign Keys from the popup menu or click on the foreign keys subtab in the Create Table tab. First, specify the name of foreign key constraint that you want to create after the CONSTRAINT keyword. Using SQLyog you create relationships on the child table that will refer to the columns of the parent table.įirst of all you need to create correct indexes on the columns of both the tables. Cuando intento ejecutarlo MySQl me da este error: Error: 150 'Foreign key constraint is incorrectly formed'. SQLyog treats one of the tables in the relationship as the child and the other parent. Corresponding ON UPDATE options are available starting from 4.0.8. If ON DELETE SET NULL is specified, the child rows are automatically updated so that the columns in the foreign key are set to the SQL NULL value. If ON DELETE CASCADE is specified, and a row in the parent table is deleted, then InnoDB automatically deletes also all those rows in the child table whose foreign key values are equal to the referenced key value in the parent row. Starting from MySQL version 3.23.50 you can also associate the ON DELETE CASCADE or ON DELETE SET NULL clause with the foreign key constraint. The length of string types need not be the same. The size and the signedness of integer types have to be the same. It depends on the MySQL version.Ĭorresponding columns in the foreign key and the referenced key must have similar internal data types so that they can be compared without a type conversion. MySQL does not always auto-create indexes on foreign keys or referenced keys: sometimes you have to create them explicitly. No matter what table type (of those supporting FK's) are used for defining the FK relationship/constraint, both tables involved in the relationship have to be same type and there must be an index where the foreign key and the referenced key are listed as the first columns.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |