SQL Data Types and Constraints

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

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

Edit Copy Hide

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

String Types

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

Edit Copy Hide

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)

Edit Copy Hide

CREATE TABLE example (
    birthdate DATE,
    last_login DATETIME
);

Boolean Type

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

Edit Copy Hide

CREATE TABLE example (
    is_active BOOLEAN
);

PRIMARY KEY

• Uniquely identifies a record in a table.

Edit Copy Hide

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

AUTO_INCREMENT

• Automatically increments the column value for each new row.

Edit Copy Hide

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

UNIQUE

• Ensures that all values in a column are unique.

Edit Copy Hide

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

NOT NULL

• Ensures that a column cannot contain NULL values.

Edit Copy Hide

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.

Edit Copy Hide

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

FOREIGN KEY

• Establishes a link between two tables using a column.

Edit Copy Hide

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