Data Types and Constraints

Introduction

Data types and constraints are essential in SQL as they define the kind of data that can be stored in tables and ensure data integrity. Understanding these concepts is crucial for designing efficient and reliable databases.

SQL Data Types

SQL supports different data types categorized into numeric, string, date/time, and other specialized types.

1. Numeric Data Types

Used for storing numbers, including integers and floating-point values.

• INT – Stores whole numbers (e.g., 1, 2, 1000)
• BIGINT – Stores large integers (useful for big datasets)
• DECIMAL(p, s) / NUMERIC(p, s) – Stores exact numeric values with precision (p) and scale (s)
• FLOAT / REAL – Stores approximate floating-point values

2. String (Character) Data Types

Used for storing textual data.

• CHAR(n) – Fixed-length string of n characters
• VARCHAR(n) – Variable-length string up to n characters
• TEXT – Large text storage (specific to some database systems)

3. Date and Time Data Types

Used for handling date and time values.

• DATE – Stores only date (YYYY-MM-DD)
• TIME – Stores only time (HH:MM:SS)
• DATETIME / TIMESTAMP – Stores both date and time

4. Other Data Types

Some SQL databases support specialized data types:

• BOOLEAN – Stores TRUE or FALSE values
• BLOB – Stores binary large objects (e.g., images, files)
• JSON – Stores JSON formatted data

SQL Constraints

Constraints are rules applied to table columns to enforce data integrity and accuracy.

1. NOT NULL Constraint

Ensures that a column cannot store NULL values.

CREATE TABLE Students (
    ID INT NOT NULL,
    Name VARCHAR(100) NOT NULL
);

2. UNIQUE Constraint

Ensures that all values in a column are unique.

CREATE TABLE Employees (
    EmpID INT UNIQUE,
    Email VARCHAR(255) UNIQUE
);

3. PRIMARY KEY Constraint

A combination of NOT NULL and UNIQUE, ensuring each row has a unique identifier.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100)
);

4. FOREIGN KEY Constraint

Establishes a relationship between two tables by linking a column to another table’s primary key.

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

5. CHECK Constraint

Limits the values that can be stored in a column.

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Price DECIMAL(10,2) CHECK (Price > 0)
);

6. DEFAULT Constraint

Sets a default value for a column when no value is provided.

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Status VARCHAR(20) DEFAULT 'Active'
);

7. AUTO_INCREMENT

Automatically generates a unique number for each row in a column.

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

8. INDEX

Speeds up data retrieval from a database table.

CREATE INDEX idx_customer_name ON customers (name);

9. CASCADE (ON DELETE & ON UPDATE)

Maintains referential integrity when a referenced record is deleted or updated.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

List of Commonly used SQL data Types

1. Numeric Data Types

• INT – Integer value
• TINYINT – Very small integer
• SMALLINT – Small integer
• MEDIUMINT – Medium-sized integer
• BIGINT – Large integer
• DECIMAL(p, s) / NUMERIC(p, s) – Fixed-point precision
• FLOAT – Floating point number
• DOUBLE – Double precision floating point

2. String (Character) Data Types

• CHAR(n) – Fixed-length string
• VARCHAR(n) – Variable-length string
• TEXT – Large text data
• TINYTEXT – Very small text
• MEDIUMTEXT – Medium-sized text
• LONGTEXT – Large text

3. Date and Time Data Types

• DATE – Stores date (YYYY-MM-DD)
• DATETIME – Stores date and time (YYYY-MM-DD HH:MM:SS)
• TIMESTAMP – Stores timestamp value
• TIME – Stores time (HH:MM:SS)
• YEAR – Stores a year (YYYY)

4. Boolean Data Type

BOOLEAN / BOOL – Stores TRUE (1) or FALSE (0)

5. Binary Data Types

• BLOB – Binary Large Object
• TINYBLOB – Very small binary object
• MEDIUMBLOB – Medium-sized binary object
• LONGBLOB – Large binary object

6. JSON and Special Data Types

• JSON – Stores JSON data (supported in MySQL & PostgreSQL)
• ENUM – Stores predefined set of values
• SET – Stores multiple predefined values

Conclusion

Understanding SQL data types and constraints is essential for designing structured and error-free databases. By selecting appropriate data types and applying constraints effectively, you can ensure data accuracy, consistency, and integrity in your database applications.