on adding foreign key, if specify constraint it won't add

Question

If I do this

  ALTER TABLE cp8_sales_order_flat 
  ADD CONSTRAINT fk_customer 
  FOREIGN KEY (customer_id) 
  REFERENCES customers(id) 
  ON DELETE SET NULL
  ON UPDATE CASCADE;

it will say it cant add foreign key error 1215

if I just write

ALTER TABLE sales_flat_order
ADD FOREIGN KEY (customer_id) REFERENCES customers(id);

then it adds the FK, and the constraint, but it is set as: restrict.

it seems to me that this would allow correct select join queries, but not deletes or updates. Why is this? any fix?

UPDATE

One of the reasons may also be that the column you are using for ON DELETE SET NULL is not defined to be null. So make sure that the column is set default null.

This is the one I think, as I checked everything else, type, collation, length, attribute

Perhaps this helps: https://stackoverflow.com/questions/16969060/mysql-error-1215-cannot-add-foreign-key-constraint — Jose Luis, Jun 04 '17 at 15:16
thanks Pepe Luis, indeed in one of the many answers there there ones the key one, that I include as the answer. — Arminius, Jun 04 '17 at 15:35

score 2 · Accepted Answer · answered Jun 04 '17 at 15:28

my column customer_id was set to be not null, while I was saying in my constraint that "on delete" set null, so this is what I call logical schizophrenia, like voting for more immigration when we have dozens of millions of unemployed people in Europe. Luckily here I can rewrite the constraint,not so in politics.

on adding foreign key, if specify constraint it won't add

1 Answers1