Create and Delete Table | Database

Introduction

Tables are used to store data in the database. Each table contains one or more columns. And each column has an associated data type that defines the kind of data it can store e.g., number, string.

To create a new table, use CREATE TABLE statement. Use DROP TABLE statement to delete one or more tables from a database.

Create Table

To create a new table, you use the CREATE TABLE statement as follows:

CREATE TABLE table_name (
  column_0 data_type constraint,
  column_1 data_type constraint,
  column_2 data_type,
  ...,
  table_constraints
);

Specify the name of the new table.
Each column has an associated data type specified after its name in the statement. A column may have one or more optional constraints such as NOT NULL and UNIQUE.
A table may optional constraints such as FOREIGN KEY, PRIMARY KEY, UNIQUE and CHECK.

A constraint is basically a rule associated with a column that the data entered into that column must follow. For example, UNIQUE constraint specifies that no two records can have the same value in a particular column. A PRIMARY KEY constraint defines a unique identification of each record (or row) in a table.

Example

CREATE TABLE Person (
  id INT PRIMARY KEY,
  first_name VARCHAR (50) NOT NULL,
  last_name VARCHAR (50) NOT NULL,
  phone VARCHAR(20)
);

id column is the primary key column of the table. first_name and last_name columns are character string columns with VARCHAR type. These columns can store up to 50 characters. phone column is a varying character string column which accepts NULL.

Delete Table

To delete a table, use the following DROP TABLE statement:

DROP TABLE [IF EXISTS] table_name;

Specify the name of the table to be removed.
Use IF EXISTS clause to remove the table only if it exists.
Dropping a table, deletes all data stored in table.

Reference

CREATE TABLE