MySQL - restrict tinyint column

Question

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...)

I don't think it's possible, like you said yourself you can create an unique index on `is_main, user_id`, but you can only have 2 addresses then (one with is_main = 1 and one with is_main = 0). You need to restrict it another way then, in your code for example. Before you insert it, do a check if there are any rows with is_main = 1. — davey, Oct 30 '13 at 12:18

score 1 · Answer 1 · edited May 23 '17 at 12:25

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. :(

MySQL - restrict tinyint column

1 Answers1