How to change the action when ON UPDATE / ON DELETE in MySQL? Here is the table now:

CONSTRAINT `FK_orders_order` FOREIGN KEY (`order_idorder`) REFERENCES `order` (`idorder`) ON UPDATE NO ACTION ON DELETE NO ACTION

But I make such a request:

 ALTER TABLE `orders` ADD CONSTRAINT `FK_orders_order` FOREIGN KEY (`order_idorder`) REFERENCES `order` (`idorder`) ON UPDATE SET NULL ON DELETE SET NULL;

I get a mistake, how to fix it? What's wrong doing

  • And before that you delete the old alter table orders drop foreign key FK_orders_order ? - Mike
  • @Mike yes, delete, this is what I write when I insert a new `SQL error (1005): Can't create table 'exchange. # Sql-1588_9' (errno: 150) Foreign key constraint is incorrectly formed *` - rodgers
  • @Mike If I create a localized Constraint (NO ACTION или RESTRICT) then it is created and if I do what I need (CASCADE или SET NULL) then throws an error - rodgers
  • Are you sure that MySQL supports on update . the directory says only about on delete mysql.ru/docs/man/SEC451.html - Mike
  • @Mike, in the original dock there is - MaxU

1 answer 1

You are trying to set SET NULL for a column with NOT NULL constraint:

 CREATE TABLE `orders` ( `idorders` INT(11) NOT NULL AUTO_INCREMENT, `order_idorder` INT(11) NOT NULL, /* !!! NOT NULL constraint !!! */ ...
  • No, the length and data type are the same - rodgers
  • Thank! Problem solved - rodgers