-1

I want to store phone numbers as integers and I've created this SQL:

DROP TABLE `user_id_phone`;
CREATE TABLE `user_id_phone`
(
    `user_id`      int     unsigned  NOT NULL,
    `country_code` smallint unsigned NOT NULL,
    `number`       bigint unsigned   NOT NULL,
    `ext`          smallint unsigned NULL,
    PRIMARY KEY (`country_code`, `number`, `ext`)    
) ENGINE = InnoDB

But my server (10.4.14-MariaDB) creates field ext as 'NOT NULL':

`ext` smallint(5) unsigned NOT NULL,

if ext is a part of the composite primary key:

PRIMARY KEY (`country_code`, `number`, `ext`) 

In case of ext is not a part of the primary key:

PRIMARY KEY (`country_code`, `number`),

the field DDL is right after creation:

  `ext` smallint(5) unsigned DEFAULT NULL,

Why?

1
  • Recommend using standard 2-letter country_codes instead of smallint.
    – Rick James
    Commented Sep 26, 2020 at 0:47

1 Answer 1

3

Why?

Because you can't have NULL values in a primary key. Why? Because the SQL standard says so:

If the <unique specification> specifies PRIMARY KEY, then for each <column name> in the explicit or implicit <unique column list> for which NOT NULL is not specified, NOT NULL is implicit in the <column definition>.

This is also described in documentation.

PS. Phone numbers are not integers and should not be stored as such. They are character sequences that often, but not always, contain mostly digits.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.