CODEWITHSUNDEEP
mysql
Fisher Coder
Fisher Coder

Reputation: 3576

MySQL table: BIT(1) not null default b'1', however, always default to '0'

This is how I created the table:

CREATE TABLE `item_spa_cust` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `spa_id` INT(10) NULL DEFAULT NULL,
    `type` VARCHAR(20) NOT NULL COLLATE 'latin1_swedish_ci',
    `is_valid` BIT(1) NOT NULL DEFAULT b'1',
    `company_name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
    `custno` VARCHAR(6) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',
    PRIMARY KEY (`id`),
    INDEX `FK_item_spa_cust_item_spa` (`spa_id`),
    CONSTRAINT `FK_item_spa_cust_item_spa` FOREIGN KEY (`spa_id`) REFERENCES `item_spa` (`id`)) 
;

I was expecting the is_valid field to default to '1' as I set it, however, it's always defaulting to '0'.

I'm very confused about this, please help.

Upvotes: 6

Views: 17578

Answers (2)

zakaria haddada
zakaria haddada

Reputation: 1

you should alter your table and modify the default value of the column to 1 instead of b'1' . this query will do the work :

ALTER TABLE table_name CHANGE COLUMN column_name BIT(1) NULL DEFAULT 1 ;

Upvotes: -1

grepsedawk
grepsedawk

Reputation: 3411

You should probably use 1, rather than b'1'

That being said, in this sqlfiddle, it works as expected.

If you're inserting and looking to default the value, you should NOT SPECIFY it in your insert query.

Upvotes: 5

Related Questions