SQL UNSIGNED

What does UNSIGNED do in SQL?

UNSIGNED is a MySQL column attribute that disallows negative values and doubles the positive range of an integer or fixed-point numeric type.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL UNSIGNED:

SQL UNSIGNED Full Explanation

UNSIGNED removes the sign bit from supported numeric column types so the full storage space represents zero or positive values. For example, an INT normally ranges from -2,147,483,648 to 2,147,483,647. Declared as INT UNSIGNED, it instead ranges from 0 to 4,294,967,295. The attribute is most common on AUTO_INCREMENT primary keys, counters, monetary amounts, and any measurement that can never be negative.UNSIGNED can be applied to TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, DECIMAL, and NUMERIC. It is ignored on floating-point types (FLOAT, DOUBLE) in MySQL 8.0+ and prohibited on non-numeric types. You can add or drop the attribute with CREATE TABLE or ALTER TABLE ... MODIFY/CHANGE. Attempting to insert or update a negative value into an UNSIGNED column triggers error 1264 (Out of range value for column).Other major databases do not implement UNSIGNED; instead, use a larger data type or check constraints. Because of this, schema migrations from MySQL to PostgreSQL or SQL Server must translate UNSIGNED columns carefully.

SQL UNSIGNED Syntax

-- In CREATE TABLE
CREATE TABLE measurements (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  clicks BIGINT UNSIGNED NOT NULL,
  price DECIMAL(10,2) UNSIGNED DEFAULT 0
);

-- In ALTER TABLE
ALTER TABLE measurements MODIFY clicks BIGINT UNSIGNED;

SQL UNSIGNED Parameters

Example Queries Using SQL UNSIGNED

-- Create table with UNSIGNED columns
CREATE TABLE users (
  user_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  age TINYINT UNSIGNED,
  balance DECIMAL(12,2) UNSIGNED DEFAULT 0
);

-- Attempt to insert a negative value (will fail)
INSERT INTO users (age) VALUES (-5);

-- Successful insert
INSERT INTO users (age, balance) VALUES (30, 150.75);

-- Add UNSIGNED to an existing column
ALTER TABLE users MODIFY balance DECIMAL(12,2) UNSIGNED;

-- Remove UNSIGNED from a column
ALTER TABLE users MODIFY age TINYINT;

Expected Output Using SQL UNSIGNED

#VALUE!

Use Cases with SQL UNSIGNED

Defining primary key or surrogate key columns that auto-increment
Storing counters such as page views, likes, or downloads
Recording monetary amounts that cannot go negative
Modeling inventory quantities, distances, or durations that are inherently non-negative

Common Mistakes with SQL UNSIGNED

Expecting UNSIGNED to exist in PostgreSQL, SQL Server, Oracle, or SQLite
Applying UNSIGNED to varchar or float columns (not supported)
Assuming UNSIGNED automatically prevents overflow when values approach the upper bound
Forgetting to adjust application code when migrating schemas to or from MySQL

First Introduced In

MySQL 3.23

Frequently Asked Questions

What happens if I insert a negative value into an UNSIGNED column?

MySQL raises error 1264 (Out of range value) and rejects the row because negative numbers violate the UNSIGNED constraint.

Does UNSIGNED save storage space?

No. Storage size is identical to the signed equivalent. The difference lies only in how the bit pattern is interpreted.

Can I combine UNSIGNED with AUTO_INCREMENT?

Yes. Using UNSIGNED on an AUTO_INCREMENT primary key is common because the sequence never needs negative values.

How do I migrate an UNSIGNED column to PostgreSQL?

Change the target column to a larger signed type (e.g., BIGINT) or add a CHECK constraint like CHECK (col >= 0) to enforce non-negativity.