CODEWITHSUNDEEP
mysqlunsignedsigned
bodacydo
bodacydo

Reputation: 79549

Should MySQL's primary key be just INTEGER or should it be UNSIGNED INTEGER (of appropriate size)?

Let's say I'm creating a table in MySQL:

CREATE TABLE animals (
    id INT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (id)
);

Should the primary key be simply INT or should I make it UNSIGNED INT? I know that a negative primary key doesn't make much sense, so I think it should be UNSIGNED INT. Although which one would be faster?

Upvotes: 2

Views: 3308

Answers (3)

user207421
user207421

Reputation: 311052

If it's auto incremented, as they usually are, it should be unsigned, otherwise you will get unexpected sequencing at the overflow point.

Upvotes: 3

spencer7593
spencer7593

Reputation: 108530

We use INT UNSIGNED or BIGINT UNSIGNED for all of our surrogate primary keys. We also use the name id for the column.

There's nothing problematic (or necessarily evil) with negative values for an integer PRIMARY KEY, a signed integer would be fine as well.

One issue to be aware of with an unsigned integer: the maximum value is LARGER than the maximum value of a signed integer. (INT UNSIGNED maximum value is 2^32-1, rather than 2^31-1.) Any client programs will need to be aware of this; mapping an INT UNSIGNED into a 32-bit signed integer (for example, a C int type) will be a problem if the value exceeds 2^31-1.

Upvotes: 3

miken32
miken32

Reputation: 42751

You can avoid thinking about it altogether:

CREATE TABLE animals (
    id SERIAL,
    name CHAR(30) NOT NULL
);

(Which does create it as an unsigned int, and also adds the unique index and auto incrementing)

Upvotes: 4

Related Questions