SQL INTEGER

What is the SQL INTEGER data type?

INTEGER is a numeric SQL data type that stores whole numbers without fractional components.

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 INTEGER: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, BigQuery, Redshift

SQL INTEGER Full Explanation

INTEGER (often written INT) is a fixed-precision signed whole-number data type defined by the SQL standard. It stores values without decimals, typically using 4 bytes of storage, giving a range of −2,147,483,648 to 2,147,483,647 in two-complement implementations. Some vendors provide UNSIGNED or ZEROFILL modifiers, but these are extensions and not portable.INTEGER is part of the broader numeric family that includes SMALLINT, BIGINT, and DECIMAL. Choosing INTEGER instead of larger or smaller types affects storage, performance, and overflow risk. In table definitions, INTEGER can have optional constraints such as PRIMARY KEY, NOT NULL, CHECK, or AUTO_INCREMENT/IDENTITY (vendor specific). Casting between INTEGER and other numeric types is allowed but truncates fractions.Because INTEGER is exact (not approximate like FLOAT), it is preferred for counts, identifiers, and any value that must remain integral. Be aware of overflow: inserting a number outside the supported range raises an error or silently wraps, depending on the database.

SQL INTEGER Syntax

column_name INTEGER [NOT NULL] [PRIMARY KEY] [DEFAULT <value>]

SQL INTEGER Parameters

Example Queries Using SQL INTEGER

-- Create a table with an INTEGER primary key
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    age INTEGER
);

-- Insert rows
INSERT INTO users (id, username, age) VALUES (1, 'alice', 30);
INSERT INTO users (id, username, age) VALUES (2, 'bob', 28);

-- Cast a decimal to INTEGER (fraction truncated)
SELECT CAST(123.99 AS INTEGER) AS whole_number;

-- Check for overflow (will fail in most systems)
INSERT INTO users (id, username) VALUES (2147483648, 'charlie');

Expected Output Using SQL INTEGER

Table is created, two rows inserted, SELECT returns 123, and the overflow insert raises an out-of-range error

Use Cases with SQL INTEGER

Store primary or surrogate keys when sequence values fit 32-bit range
Keep counters such as page views, inventory quantities, votes
Represent attributes that can never be fractional, like age or year
Improve performance over BIGINT when values are small enough

Common Mistakes with SQL INTEGER

Assuming INTEGER is always 4 bytes; some systems allow 2 or 8 bytes with same keyword
Ignoring signed range limits and causing overflow
Expecting fractional parts to round instead of truncate when casting to INTEGER
Using INTEGER for monetary values instead of DECIMAL, causing scale issues

First Introduced In

SQL-86

Frequently Asked Questions

What is the exact storage size of INTEGER?

Most databases allocate 4 bytes, but some allow configurable sizes while keeping the INTEGER keyword.

Can INTEGER be unsigned in standard SQL?

The SQL standard does not define UNSIGNED. MySQL and MariaDB add an UNSIGNED modifier that disallows negative values and doubles the positive range.

How do I avoid overflow with INTEGER counters?

Choose BIGINT if the counter can exceed the 32-bit limit, or add a CHECK constraint to catch near-overflow values early.

Is INTEGER suitable for money values?

No. Use DECIMAL or NUMERIC with an explicit scale to maintain precision for currency calculations.