CODEWITHSUNDEEP
mysqlconstraintsrestriction
eez
eez

Reputation: 31

MySQL - restrict tinyint column

In MySQL db, I have a table with users' addresses:

CREATE TABLE `user_address` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `user_id` INT(10) NOT NULL,
    `street` VARCHAR(50) NULL DEFAULT NULL,
    `number` VARCHAR(255) NULL DEFAULT NULL,
    `city` VARCHAR(255) NULL DEFAULT NULL,
    `zip` VARCHAR(255) NULL DEFAULT NULL,
    `is_main` TINYINT(1) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
)

The number of addresses per user is not limited, but I want the is_main to be 1 only once per user, to indicate the main user's address. All other user's addresses will have is_main = 0.

Can this be restricted anyhow on the MySQL level?

(ALTER TABLE user_address ADD UNIQUE INDEX is_main (is_main, user_id); didn't help as there are several records with is_main=0...)

Upvotes: 1

Views: 1116

Answers (1)

cautionbug
cautionbug

Reputation: 475

i came across this looking for something similar... i think you (and likely me in my search) are mistaking TINYINT(1) for BIT(1).

See here: MySQL Boolean "tinyint(1)" holds values up to 127?

and here: Difference between "int" and "int(3)" data types in my sql

TINYINT is a byte-storage type (as are the other ints), where defining the length (TINYINT(1)) only affects the displayed value, not the actual stored value. The stored size is defined by the type itself (MySql Doc)

BIT is more like string types in that the length (BIT(1)) defines how many will be stored (like CHAR(5) will store exactly 5 characters). So BIT(1) is more akin to a real boolean.

For your purposes, a simple unsigned TINYINT in the unique key may be sufficient, unless you anticipate users having more than 255 addresses. However, it will be up to your application to identify the 0-valued row as the main address (which kinda warps the semantics of the name is_main, so perhaps "address_index"?). i think it'll be a challenge even then if the main address is allowed to change (which is logical). You may need to set some controls with triggers so that when an address record is set to "main" (0), all others for that user are updated to distinct non-0 values. Bleh. :(

Upvotes: 1

Related Questions