This site is for tech Q&A. Please keep your posts focused on the subject at hand.

Ask one question at a time. Don't conflate multiple problems into a single question.

Make sure to include all relevant information in your posts. Try to avoid linking to external sites.

Links to documentation are fine, but in addition you should also quote the relevant parts in your posts.

1 vote

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?

in Sysadmin
recategorized by
edit history

Please log in or register to answer this question.

1 Answer

0 votes

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:

    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.

by (115)
2 18 33
edit history