Creating Tables

Introduction

In this section, we will explore how to create tables using Structured Query Language (SQL). A table is a fundamental component of relational databases, used to store and organize data. Understanding how to create tables is essential for any developer working with SQL databases.

Objectives

By the end of this tutorial, you should be able to:

• Understand the basic syntax for creating tables.
• Define columns with appropriate data types.
• Apply constraints to ensure data integrity.
• Create tables using real-world examples.

Basic Syntax

The CREATE TABLE statement is used to create a new table in a database. The basic syntax is as follows:

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    ...
);

Key Components

• table_name: The name of the table you want to create.
• column: Each column has a name and a data type. You can also apply constraints to each column.

Data Types

Data types define the kind of data that can be stored in a column. Common data types include:

• INT or INTEGER: For integer values.
• VARCHAR(n) or CHAR(n): For variable-length or fixed-length character strings, respectively.
• DATE: For date values.
• BOOLEAN: For true/false values.

Example

Let's create a simple table named employees with the following columns:

• id (integer)
• first_name (string)
• last_name (string)
• email (string)
• hire_date (date)

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    hire_date DATE
);

Constraints

Constraints are used to enforce rules on the data in a table. Common constraints include:

• PRIMARY KEY: Uniquely identifies each record in the table.
• NOT NULL: Ensures that a column cannot have null values.
• UNIQUE: Ensures that all values in a column are unique.
• FOREIGN KEY: Establishes a link between two tables.

Example with Constraints

Let's modify the employees table to include constraints:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    hire_date DATE NOT NULL
);

Advanced Table Creation

Auto Increment

In some databases, you can use the AUTO_INCREMENT attribute to automatically generate a unique value for each new record.

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    hire_date DATE NOT NULL
);

Default Values

You can set default values for columns using the DEFAULT keyword.

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    hire_date DATE DEFAULT CURRENT_DATE
);

Real-World Example

Let's create a more complex table for an e-commerce application. We'll create a products table with the following columns:

• product_id
• name
• description
• price
• category_id
• created_at

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    category_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

In this example, the products table has a foreign key constraint linking to a categories table, ensuring referential integrity.

Best Practices

• Use Descriptive Names: Choose meaningful names for tables and columns.
• Define Constraints: Use constraints to enforce data integrity and avoid errors.
• Normalize Data: Design your database schema in a normalized form to reduce redundancy and improve performance.
• Document Your Schema: Maintain clear documentation of your database schema, including table structures and relationships.

Conclusion

Creating tables is a foundational skill in SQL & Databases. By understanding the syntax, data types, and constraints, you can effectively design and manage relational databases. Practice creating tables with different schemas to reinforce your learning.

In the next section, we will explore how to insert data into these tables and perform basic queries. Stay tuned!