MYSQL foreign key on delete set NOT NULL (default, 0) - how to fix?

Question

MySQL

CREATE TABLE IF NOT EXISTS product_category (
  id int unsigned NOT NULL AUTO_INCREMENT,
  name varchar(100) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY (name),
  UNIQUE KEY (url)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS product (
  id int unsigned NOT NULL AUTO_INCREMENT,
  name varchar(100) NOT NULL,
  category_id int unsigned NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (category_id) REFERENCES product_category (id) ON DELETE SET NULL
) ENGINE=InnoDB;

Is it possible to make it so that when the parent table is deleted, the value of the child's associated field is set to 0 or DEFAULT? SET NULL is unsatisfactory because it requires the data type to be changed from NOT NULL to NULL, otherwise it throws an error.
SET '0', SET 0, SET DEFAULT also throw an error.

Answer 1

There is no such possibility, and if there is one in some databases, this is a big mistake, since it violates the referential integrity of the data.
The foreign key must either have an existing value or NULL. == "value unknown".
Changing the data type from NOT NULL to NULL will help you, I don't understand why it bothers you.

Answer 2

The scheme as a result turned out as follows (I will be glad to any comments):

CREATE TABLE IF NOT EXISTS user (
  id int unsigned AUTO_INCREMENT,
  regdate timestamp,
  lastvisit timestamp,
  email varchar(100),
  password varchar(128),
  salt varchar(20),
  status tinyint,
  level tinyint,
  balance decimal(10,2),
  PRIMARY KEY (id),
  UNIQUE KEY (email)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_profile (
  id int unsigned AUTO_INCREMENT,
  uid int unsigned NOT NULL,
  firstname timestamp,
  PRIMARY KEY (id),
  UNIQUE KEY (uid),
  FOREIGN KEY (uid) REFERENCES user (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_activation (
  id int unsigned AUTO_INCREMENT,
  uid int unsigned NOT NULL,
  expire timestamp,
  hash varchar(20),
  PRIMARY KEY (id),
  FOREIGN KEY (uid) REFERENCES user (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_reset (
  id int unsigned AUTO_INCREMENT,
  uid int unsigned NOT NULL,
  expire timestamp,
  hash varchar(20),
  PRIMARY KEY (id),
  FOREIGN KEY (uid) REFERENCES user (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_session (
  id int unsigned AUTO_INCREMENT,
  uid int unsigned NOT NULL,
  expire timestamp,
  hash varchar(20),
  ip varchar(39),
  agent varchar(200),
  PRIMARY KEY (id),
  FOREIGN KEY (uid) REFERENCES user (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS product_category (
  id int unsigned AUTO_INCREMENT,
  name varchar(100),
  url varchar(100),
  PRIMARY KEY (id),
  UNIQUE KEY (name),
  UNIQUE KEY (url)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS product_brand (
  id int unsigned AUTO_INCREMENT,
  name varchar(100),
  url varchar(100),
  PRIMARY KEY (id),
  UNIQUE KEY (name),
  UNIQUE KEY (url)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS product (
  id int unsigned AUTO_INCREMENT,
  created timestamp,
  edited timestamp,
  name varchar(100),
  description text,
  category_id int unsigned NULL,
  brand_id int unsigned NULL,
  url varchar(100),
  cost decimal(10,2),
  quantity int unsigned,
  PRIMARY KEY (id),
  FOREIGN KEY (category_id) REFERENCES product_category (id) ON DELETE SET NULL,
  FOREIGN KEY (brand_id) REFERENCES product_brand (id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS product_photo (
  id int unsigned AUTO_INCREMENT,
  product_id int unsigned NOT NULL,
  url varchar(100),
  order_id int unsigned,
  PRIMARY KEY (id),
  UNIQUE KEY (url),
  FOREIGN KEY (product_id) REFERENCES product (id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS product_tag (
  id int unsigned AUTO_INCREMENT,
  name varchar(100),
  PRIMARY KEY (id),
  UNIQUE KEY (name)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS product_tag_relation (
  id int unsigned AUTO_INCREMENT,
  product_id int unsigned NOT NULL,
  tag_id int unsigned NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (product_id) REFERENCES product (id) ON DELETE CASCADE,
  FOREIGN KEY (tag_id) REFERENCES product_tag (id) ON DELETE CASCADE
) ENGINE=InnoDB;