SQL CREATE Table

Introduction

In this chapter, we will learn how to create tables in SQL. Tables are essential components of a database, as they store the actual data. Creating a table involves defining the columns and their data types.

Creating a Table

The CREATE TABLE statement is used to create a new table in a database. This statement defines the structure of the table, including its columns and their data types.

Syntax

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

• table_name: The name of the table.
• column1, column2, ...: The names of the columns in the table.
• datatype: The type of data the column can hold (e.g., INT, VARCHAR, DATE).
• constraints: Optional constraints on the column (e.g., PRIMARY KEY, NOT NULL).

Example

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

• id: An integer that uniquely identifies each employee (primary key).
• first_name: A string that stores the employee's first name.
• last_name: A string that stores the employee's last name.
• email: A string that stores the employee's email address.

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

Column Data Types

Common data types used in SQL tables include:

• INT: Integer data.
• VARCHAR(n): Variable-length string data with a maximum length of n.
• DATE: Date data.
• FLOAT: Floating-point number.
• BOOLEAN: Boolean data (TRUE or FALSE).

Adding Constraints

Constraints are rules applied to the columns in a table to ensure data integrity. Common constraints include:

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

Example with Constraints

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE DEFAULT CURRENT_DATE,
    salary DECIMAL(10, 2) CHECK (salary > 0)
);

In this example:

• first_name and last_name cannot be NULL.
• email must be unique.
• hire_date defaults to the current date.
• salary must be a positive number.

Creating a Table with Foreign Key

A foreign key is a column or a set of columns that establishes a link between data in two tables. It is used to maintain referential integrity.

Example with Foreign Key

Let's create a departments table and then an employees table that references the departments table.

CREATE TABLE departments (
    department_id INT PRIMARY KEY AUTO_INCREMENT,
    department_name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

In this example:

• The departments table is created with department_id as the primary key.
• The employees table includes a department_id column that references department_id in the departments table.

Viewing the Table Structure

You can view the structure of a table using the DESCRIBE statement.

Syntax

DESCRIBE table_name;

Example

DESCRIBE employees;

Output

Field	Type	Null	Key	Default	Extra
id	int(11)	NO	PRI	NULL	auto_increment
first_name	varchar(50)	NO		NULL
last_name	varchar(50)	NO		NULL
email	varchar(100)	YES	UNI	NULL
hire_date	date	YES		CURRENT_TIMESTAMP
salary	decimal(10,2)	YES		NULL
department_id	int(11)	YES	MUL	NULL

Example for departments Table

DESCRIBE departments;

Output

Field	Type	Null	Key	Default	Extra
department_id	int(11)	NO	PRI	NULL	auto_increment
department_name	varchar(50)	NO		NULL

Conclusion

Creating tables is a fundamental part of working with databases. This chapter covered the CREATE TABLE statement, including how to define columns, data types, and constraints. We also explored creating tables with foreign keys to establish relationships between tables. Viewing the structure of tables using the DESCRIBE statement helps you verify the table definitions.