Datatypes

Winning with SQL: Essential Datatypes and Constraints Demystified

by

Introduction

The E.F. Codd rules were discussed in the last article. The final point, if we recall correctly, was to validate the data before putting it in a table by assigning:

  1. Datatypes
  2. Constraints

Now, we will discuss datatypes and constraints, how they validate the data, their uses in real-world applications, advantages, and disadvantages.

What is a Datatype?

Datatypes specify what type of data should be entered into the column of a table. In calculating, data is categorized into different types such as:

  • Numeric Data (Whole numbers, real numbers, complex numbers)
  • String Data (Characters, text)
  • Binary Data (Images, files)

Data Types in SQL

SQL supports various data types, which can be divided into:

Binary Data Type

Used to store images and other binary data.

Numeric Data Types

Further divided into:

Approximate Numeric Data Types:

  • REAL
  • FLOAT (supports decimal points, difference lies in range)

Exact Numeric Data Types:

  • BIT (0 or 1)
  • TINYINT (a small range of values)
  • SMALLINT (larger than TINYINT, but smaller than INTEGER)
  • INT or INTEGER (standard whole numbers)
  • BIGINT (supports large numbers)
  • DECIMAL (for precise decimal values)

String Data Types

Used to store text-based data:

  • CHAR – Fixed-length string
  • VARCHAR – Variable-length string (size can be modified)
  • TEXT – Large blocks of text

Alphanumeric Data

Example: PAN number (stores both letters & numbers)

Date and Time Data Types

Used to store date and time values:

  • DATE – Stores year, month, and day (YYYY-MM-DD)
  • TIME – Stores hours, minutes, and seconds (HH:MM:SS)
  • DATETIME – Stores both date and time
Datatypes
Datatypes

Choosing the Right Data Type

When designing a database, attributes in tables must be assigned an appropriate data type based on the data they store. For example:

Column NameSuggested Data Type
Student_IDINTEGER
Student NameVARCHAR (30)
PercentageFLOAT

Common SQL Data Types Supported Across DBMS Tools

Since SQL is used in different Database Management Systems (DBMS) like MySQL, SQL Server, and Oracle, some data types are universally supported:

  • FLOAT – For real numbers with decimal values
  • INTEGER / NUMBER – For whole numbers
  • CHAR / VARCHAR(size) – For text-based data
  • DATE – For storing dates
  • TIME – For storing time
  • DATETIME – For storing both date and time

Types of Datatypes in SQL (In Detail)

1. CHAR Datatype

  • Accepts ‘A-Z’, ‘a-z’, ‘0-9′, special characters, and alphanumeric values’.
  • Character values should be enclosed with single quotes (”).
  • This datatype follows Fixed-length memory allocation.
  • The Maximum number of characters that can be stored in this datatype is: 1-2000.

Example:

CHAR(10)
InputStored Value
‘ABCDE’‘ABCDE ‘ (wastes memory)

2. VARCHAR Datatype

  • Accepts ‘A-Z’, ‘a-z’, ‘0-9′, special characters, and alphanumeric values’.
  • Character values should be enclosed with single quotes (”).
  • This datatype follows Variable-length memory allocation.
  • The Maximum number of characters that can be stored in this datatype is: 1-2000.

Example:

VARCHAR(10)
InputStored Value
‘RMNOP’‘RMNOP’ (no wasted memory)

3. VARCHAR2 Datatype

  • Updated version of VARCHAR.
  • Stores up to 4000 characters.
  • Uses variable-length memory allocation.

Difference between CHAR and VARCHAR

FeatureCHARVARCHAR
Memory AllocationFixed-lengthVariable-length
SyntaxCHAR(size)VARCHAR(size)
Maximum Size (Oracle)1 to 2000 bytes1 to 4000 bytes
Storage EfficiencyWastes space if the data is shorter than the defined sizeIt uses only the required space, saving storage
PerformanceFaster for fixed-size data due to direct memory accessSlightly slower due to variable length handling

4. NUMBER Datatype

  • It is used Stores numeric values.
  • Precision: It is used to determine the number of digits to store an integer value.
  • The range of precision is 1-38.
  • Scale: It is used to determine the number of digits to store decimal values within precision.
  • The range of scale is 0-127.
  • The default value of scale is 0.
  • Based on the large number, the memory blocks will be allocated.
  • Number datatype will perform a round of operation.

Syntax:

NUMBER (precision, [scale])
ExamplePrecisionScaleStored Value
NUMBER(5)5099999
NUMBER(5,2)52999.99

5. DATE Datatype

  • Store dates in the format:
    • DD-MM-YYYY
    • DD-MM-YY

6. Large Object Datatypes

  • CLOB (Character Large Object): Stores up to 4GB of text.
  • BLOB (Binary Large Object): Stores binary data like photos, videos, and documents (up to 4GB).

Constraints in SQL

A constraint is a condition or restriction that we apply to a database. This implies that certain restrictions or conditions are applied before data is entered into the database, and the user can only add data after meeting those requirements.

Setting up a Gmail account is a straightforward real-world illustration of this. Nearly everyone has a Gmail account; if not, they may have a Yahoo Mail account. We enter our preferred username, like varunsingla@gmail.com, when creating a mail ID. The system then determines if this mail ID is accessible. Since someone else may have already taken a username, there is no guarantee that any will be available. The system accepts the username if it is available; if not, it requests that the user modify the name by adding a numeric value, date of birth, or any other modification to make it unique.

No two users on the same mail server can have the same Gmail ID because this requirement was implemented to ensure uniqueness. The same rules apply when making a password: it must contain a capital letter, at least one numeric value, special characters like @ or #, and be at least eight or ten characters long. These restrictions ensure that users enter data in compliance with established standards.

When creating a table or database in SQL, users are not permitted to enter any random data. Only data that satisfies the conditions we apply to columns (attributes) can be stored, guaranteeing the integrity and accuracy of the database.

Constraints are rules assigned to columns to validate the data.

Types of Constraints

  1. UNIQUE Constraint
    • Ensures values are unique (no duplicates).
    Syntax: UNIQUE (column_name)
  2. NOT NULL Constraint
    • Ensures the column cannot store NULL values.
    Syntax: column_name NOT NULL
  3. CHECK Constraint
    • Applies additional conditions to column values. Ensures values are unique (no duplicates).

    Syntax: CHECK (column_name condition)
    Example: CHECK (Age >= 18) CHECK (LENGTH (phone_number) = 10)
  4. PRIMARY KEY
    • Uniquely identifies each record. Combination of UNIQUE & NOT NULL. A table can have only one PRIMARY KEY.
    Syntax: PRIMARY KEY (column_name)
  5. FOREIGN KEY
    • Establishes a relationship between two tables. A column must be a PRIMARY KEY in the parent table.

    Syntax: CONSTRAINT fkref_name FOREIGN KEY (column_name) REFERENCES parent_table(column_name)

Difference Between Primary Key and Foreign Key

Primary KeyForeign Key
Identifies each record uniquelyEstablishes a connection between tables
Combination of UNIQUE & NOT NULLNot a combination of UNIQUE & NOT NULL
A table can have only oneA table can have multiple
Highly recommendedMandatory when connecting tables

Conclusion

In this article, we discussed datatypes and constraints in SQL, their types, and why they are essential for database design. How they are useful in real-world application creation.

1. SQL Data Types Classification

SQL Data Types
│
├── Binary Data Type
│   ├── BLOB (Binary Large Object)
│   ├── VARBINARY
│   └── BINARY
│
├── Numeric Data Types
│   ├── Exact Numeric
│   │   ├── BIT
│   │   ├── TINYINT
│   │   ├── SMALLINT
│   │   ├── INT / INTEGER
│   │   ├── BIGINT
│   │   ├── DECIMAL
│   │   ├── NUMERIC
│   │   └── MONEY
│   │
│   ├── Approximate Numeric
│   │   ├── FLOAT
│   │   └── REAL
│
├── String Data Types
│   ├── CHAR
│   ├── VARCHAR
│   ├── VARCHAR2
│   ├── TEXT
│   ├── CLOB (Character Large Object)
│
├── Alphanumeric Data Types
│   ├── PAN Number
│   ├── Aadhar Number
│
└── Date and Time Data Types
    ├── DATE
    ├── TIME
    ├── DATETIME
    ├── TIMESTAMP

2. CHAR vs. VARCHAR vs. VARCHAR2 Comparison

+-------------+--------------+--------------+--------------+
| Feature     | CHAR         | VARCHAR      | VARCHAR2     |
+-------------+--------------+--------------+--------------+
| Storage     | Fixed-length | Variable-length | Enhanced  |
| Max Size    | 2000 bytes   | 2000 bytes   | 4000 bytes  |
| Performance | Fast         | Slower       | Optimized   |
| Space Usage | Wastes space | Saves space  | More efficient |
+-------------+--------------+--------------+--------------+

Example:

CHAR(10)  →  'ABC     '  (wastes memory)  
VARCHAR(10)  →  'ABC'  (no wasted memory)

3. SQL Constraints Overview

SQL Constraints
│
├── UNIQUE Constraint
│   ├── Ensures no duplicate values in a column
│   └── Syntax: UNIQUE (column_name)
│
├── NOT NULL Constraint
│   ├── Ensures a column cannot have NULL values
│   └── Syntax: column_name NOT NULL
│
├── CHECK Constraint
│   ├── Adds conditions to a column’s values
│   ├── Example: CHECK (Age >= 18)
│   └── Example: CHECK (LENGTH(phone_number) = 10)
│
├── PRIMARY KEY Constraint
│   ├── Uniquely identifies each record
│   ├── Combination of UNIQUE & NOT NULL
│   └── Syntax: PRIMARY KEY (column_name)
│
└── FOREIGN KEY Constraint
    ├── Establishes relationships between tables
    ├── References a PRIMARY KEY in another table
    └── Syntax: FOREIGN KEY (column_name) REFERENCES parent_table(column_name)

4. Primary Key vs. Foreign Key Relationship

Table: Students
+------------+------------+------------+
| Student_ID | Name       | Course_ID  |
+------------+------------+------------+
| 1          | John       | 101        |
| 2          | Alice      | 102        |
+------------+------------+------------+
    ↑
    │
Primary Key (Student_ID)

Table: Courses
+------------+-------------+
| Course_ID  | Course_Name |
+------------+-------------+
| 101        | SQL Basics  |
| 102        | Advanced DB |
+------------+-------------+
    ↑
    │
Foreign Key (Course_ID) REFERENCES Students(Course_ID)

Differences:

+--------------+----------------------+
| Primary Key  | Foreign Key          |
+--------------+----------------------+
| Uniquely identifies each record | Links two tables |
| Only one per table | Can have multiple |
| Cannot be NULL | Can have duplicate values |
+--------------+----------------------+

Leave a Comment