Can I use a negative number for MySQL Integer data type?

Question

I would like to create a special username, like Community account in Stackexchange system. The Community account there has a negative userid (-1).

I know my math teacher may hate me but can I use a negative number, for example -1, for a MySQL column whose data type is Integer?

Answer 1

Generally speaking you can, since integer data types in MySQL are signed by default. You have to specify the unsigned keyword to create and unsigned integer field:

CREATE TABLE foo (
    col1  int,               -- signed integer (range -2147483648..2147483647)
    col2  int unsigned,      -- unsigned integer (range 0..4294967295)
    ...
);

However, in practice it depends on how the respective field in your database table is defined. With Question2Answer for instance (to pick a totally random example) the user ID field is defined as an unsigned integer:

MariaDB [db]> describe qa_users;
+---------------+----------------------+------+-----+---------+----------------+
| Field         | Type                 | Null | Key | Default | Extra          |
+---------------+----------------------+------+-----+---------+----------------+
| userid        | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
...

You could try changing the type of that field to signed integer

ALTER TABLE qa_users CHANGE userid userid int(10) NOT NULL AUTO_INCREMENT;

but that will fail if there are foreign key constraints referencing this field. You'd then get an error like this:

ERROR 1833 (HY000): Cannot change column 'userid': used in a foreign key constraint 'qa_edit_history_ibfk_2' of table 'db.qa_edit_history'

So you'd have to

1. drop the foreign key constraints,
2. change the type of the user ID field in the users table,
3. change the type of all fields referencing the user ID field accordingly (which might be referenced by other foreign key constraints),
4. re-create the foreign key constraints as they were.

It's probably not worth the effort just to have a "Community" account with a user ID -1.