primary key should always be unsigned?

Question

since a primary key (identifier) wont be under 0, i guess it should always be unsigned?

Answer 1

Right. Among other reasons, it would make sense to leave it unsigned.

Answer 2

Why exactly are you presuming that a primary key won't be under 0? That is not a given. I think you are confusing it with an identity column.

In any case it should make no appreciable difference either way, map the data type to the type of data you expect in the column regardless of whether it is a primary key or not.

Answer 3

Actaully identifier fields (this is an autogenerated field in mysql, the equivalentof an identity in SQL Server?) are often signed, It doubles the number of potential records. We have negative ids in our system right now.

Answer 4

MySQL suppors an optional SERIAL data type (presumably for compatibility with PostgreSQL, since SERIAL is not standard ANSI SQL). This data type is just shorthand that creates a BIGINT UNSIGNED.

Go ahead try it:

CREATE TABLE test.foo (foo_id SERIAL PRIMARY KEY);

SHOW CREATE TABLE test.foo;

CREATE TABLE `test`.`foo` (
  `foo_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`foo_id`),
  UNIQUE KEY `foo_id` (`foo_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

You get the same number of distinct values whether you declare an integer signed or unsigned: 2³² for an INT and 2⁶⁴ for a BIGINT. If the number is unsigned, you get values from 0 to that max value minus one. If the number is signed, you get values from -max/2 to max/2-1. Either way, you get the same absolute number of distinct values.

But since AUTO_INCREMENT starts at zero by default and increments in the positive direction, it's more convenient to utilize the positive values than the negative values.

But it hardly matters that you get 2X as many positive values. Any table that would exceed the maximum signed integer value 2³¹-1 is likely to continue to grow, so you should just use a BIGINT for these tables.

You're really, really, really unlikely to allocate more than 2⁶³-1 primary key values, even if you delete all your rows and re-load them many times a day.

Answer 5

NO - a primary key wont always be unsigned for example:

 create table user_status
 (
  status_id tinyint not null primary key,
  name varchar(64) not null,
  msg varchar(255) default null
 )engine=innodb;

 insert into user_status values 
    (-99,'banned', 'Account banned'), 
    (-2,'closed', 'Account closed'),
    (-1,'unverified', 'Account not verified'),
    (0,'suspended','Account suspended'),
    (1,'active', null);     

if this was an orders table however i'd use order_id int unsigned