Search code examples
mysqlforeign-keysdefault-valuealter

Mysql changing column from 'allow null' to 'not null' and setting value

CREATE TABLE `product` (
  `product_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `category_id` int(10) unsigned DEFAULT NULL,
  CONSTRAINT `product_category` FOREIGN KEY (`category_id`) REFERENCES `category` (`category_id`),
) ENGINE=InnoDB;

CREATE TABLE `category` (
  `category_id` int(10) unsigned NOT NULL AUTO_INCREMENT
) ENGINE=InnoDB;

INSERT INTO `category` VALUES (1), (2);
INSERT INTO `product` (`product_id`, `category_id`) VALUES (1, 1), (2,2), (3, NULL);

I want to change definition of column category_id to forbid NULL values and make value 1 as default value.

I'm running following query:

ALTER TABLE `product` CHANGE `category_id` `category_id` INT(10) UNSIGNED NOT NULL DEFAULT 1;

Table product containts next values (after successful alter query):

1 1
2 2
3 0

Desired result:

1 1 
2 2
3 1

FOREIGN_KEY_CHECKS is set to 1 and doesn't change.

Could someone explain why 0 is being set instead of 1.

Solution

  • Thats why the undefined value for an unsigned int is zero. The default value is only used at insertstatement.