SQL Create Table

Overview

In SQL, a table is a collection of related data entries organized in a structured format. Each table consists of rows and columns, where columns represent attributes of the data and rows represent records.

Creating a Table

To create a table in SQL, you use the CREATE TABLE statement followed by the table name and the definition of its columns and data types.

Basic Syntax

CREATE TABLE table_name (
    column1_name column1_datatype constraints,
    column2_name column2_datatype constraints,
    ...
);

Example: Creating a Simple Table

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

In this example:

• id: An integer that auto-increments with each new record and serves as the primary key.
• username: A string (up to 50 characters) that cannot be null.
• email: A string (up to 100 characters) that cannot be null and must be unique.
• created_at: A timestamp that defaults to the current date and time when a new record is created.

Data Types

Here are some commonly used SQL data types:

• INT: Integer data type.
• VARCHAR(n): Variable-length string with a maximum length of n.
• TEXT: Large text field.
• DATETIME: Date and time value.
• DECIMAL(p, s): Exact numeric data type with p total digits and s digits after the decimal point.

Constraints

You can apply various constraints when creating tables to enforce data integrity:

• PRIMARY KEY: Uniquely identifies each record in the table.
• UNIQUE: Ensures all values in a column are unique.
• NOT NULL: Ensures a column cannot have a null value.
• DEFAULT: Sets a default value for a column if no value is specified.

Example: Creating a Products Table

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Conclusion

Creating tables is fundamental in SQL database management. Understanding how to define data types and constraints helps ensure data integrity and improves database performance.