SQL Data Types and Constraints

In MySQL, data types define the type of data that a column can store, and constraints specify rules that limit the type of data that can be inserted into a column.

Here's an overview of common data types and constraints in MySQL :

Common Data Types

Numeric Types

INT - Integer
TINYINT - Tiny integer
SMALLINT - Small integer
MEDIUMINT - Medium-sized integer
BIGINT - Big integer
FLOAT - Single-precision floating-point
DOUBLE - Double-precision floating-point
DECIMAL - Fixed-point decimal


CREATE TABLE example (
    age INT,
    salary DECIMAL(10,2)
);

String Types

CHAR - Fixed-length character
VARCHAR - Variable-length character
TEXT - Variable-length text


CREATE TABLE example (
    name VARCHAR(50),
    description TEXT
);

Date and Time Types

DATE - Date (YYYY-MM-DD)
TIME - Time (HH:MM:SS)
DATETIME - Date and time (YYYY-MM-DD HH:MM:SS)
TIMESTAMP - Auto-updated timestamp
YEAR - Year (4 digits)


CREATE TABLE example (
    birthdate DATE,
    last_login DATETIME
);

Boolean Type

BOOLEAN - Synonym for TINYINT(1) or BIT(1)


CREATE TABLE example (
    is_active BOOLEAN
);

Common Constraints

PRIMARY KEY

Uniquely identifies a record in a table.


CREATE TABLE example (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

AUTO_INCREMENT

Automatically increments the column value for each new row.


CREATE TABLE example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

UNIQUE

Ensures that all values in a column are unique.


CREATE TABLE example (
    email VARCHAR(100) UNIQUE,
    username VARCHAR(50)
);

NOT NULL

Ensures that a column cannot contain NULL values.


CREATE TABLE example (
    username VARCHAR(50) NOT NULL,
    password VARCHAR(100) NOT NULL
);

CHECK

Enforces a condition on the values that can be entered into a column.


CREATE TABLE example (
    age INT CHECK (age >= 18),
    email VARCHAR(100)
);

FOREIGN KEY

Establishes a link between two tables using a column.


CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_id INT,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

These are some common data types and constraints in MySQL. The examples provided demonstrate how to use them when creating tables. Always refer to the official MySQL documentation for the most accurate and detailed information