SQL Reference - UNSIGNED

Example

CREATE TABLE test_table (
     id INT UNSIGNED);

Output

Query OK, 0 rows affected (0.11 sec)

Explanation

The id column is created as an INT UNSIGNED. This means that the id column can hold any integer between 0 and 4294967295.

Example

CREATE TABLE Test (
    ID INT UNSIGNED,
    Name VARCHAR(255)
);

INSERT INTO Test (ID, Name)
VALUES (1, 'Data 1'), (-2, 'Data 2'), (3, 'Data 3');

Output

Msg 2739, Level 16, State 1, Line 2
Column 'ID' in table 'Test' cannot be of a type that is invalid for use as a key column in an index.

Explanation

The SQL Server does not have ‘UNSIGNED’ integer data type. If we try to create a table using ‘UNSIGNED’ integer data type, we’ll receive an error: Column ‘ID’ in table ‘Test’ cannot be of a type that is invalid for use as a key column in an index.

Example

CREATE TABLE employees (
    id NUMBER(10) CONSTRAINT employees_id_nn NOT NULL,
    name VARCHAR2(100) CONSTRAINT employees_name_nn NOT NULL,
    age NUMBER(3,0) CONSTRAINT employees_age_nn NOT NULL
);

INSERT INTO employees (id, name, age) VALUES (1, 'John Smith', -30);

Output

ORA-02290: check constraint (EMPLOYEES_AGE_NN) violated

Explanation

In Oracle, there is no direct UNSIGNED attribute for numeric data types, which ensures a non-negative value. Instead, a check constraint is used to enforce non-negative values. In the given example, the age column has a check constraint. Upon trying to insert a negative value for age, Oracle throws a check constraint violation error.