SQL - Create Table in the Database
The CREATE statements are used to create the database structures like table, view, sequence, function, procedure, package, trigger, etc. We will go and explore all of these database structures in the later part of the tutorials.
The CREATE TABLE statement is used to create a new table in the database. The following is the syntax to create a new table in the database.
CREATE TABLE table_name( column_name1 data_type [NULL|NOT NULL], column_name2 data_type [NULL|NOT NULL], ... );
In the above CREATE TABLE syntax,
table_name is the name of the table you want to give,
column_name1 is the name of the first column,
column_name2 would be the name of the second column, and so on.
data_type is the type of data a column is going to be stored e.g string, integer, datetime, etc. The data types vary from database to database, for example, string datatype in SQL Server is
whereas it is
varchar2 in Oracle.
So, data types are depend on the database you are using.
NOT NULL constraint to specify whether a column allows NULL values or not. By default, all the columns allow
NULL values unless specified
NOT NULL. The
NULL columns are optional, whereas
NOT NULL columns are mandatory while inserting or updating data.
Create Table in SQL Server, MySQL, PostgreSQL, SQLite
The following command will be used to create the
Employee table in SQL Server, MySQL, PostgreSQL, SQLite database:
CREATE TABLE Employee( EmpId integer, FirstName varchar(20), LastName varchar(20), Email varchar(25), PhoneNo varchar(25), Salary integer );
Employee is the name of the table, and
Salary are the columns.
varchar is the string data type with size mentioned in the parenthesis e.g.
varchar(20) specifies that the column will store a string upto 20 characters long.
Most of the time, all the tables in the database will have at least one column as a primary key. The following defines a table with a primary key.
EmpId integer PRIMARY KEY, creates the
EmpId column and also defines it as a primary key at the same time.
CREATE TABLE Employee ( EmpId integer PRIMARY KEY, FirstName varchar(20), LastName varchar(20), Email varchar(25), PhoneNo varchar(25), Salary integer );
Create Table in Oracle
The following SQL script creates the
Employee table in Oracle Database with a primary key:
CREATE TABLE Employee( EmpId number PRIMARY KEY, FirstName varchar2(20), LastName varchar2(20), Email varchar2(25), PhoneNo varchar2(25), Salary number(8) );
Note that, in Oracle, the string data type is called
varchar2, and integer type is called
At this stage, the
Employee table is empty and does not contain any
data. Also, note that we created a table with
EmpId column as a Primary Key,
EmpId will be Unique and NOT NULL field in all the rows in the
The following is the illustration of the
Employee table in the database.
Create New Table from Existing Table
CREATE TABLE AS command is used to create a new table from an existing table with the structure and data, as shown below:
The Following queries will work in Oracle, MYSQL, SQLite, and PostgreSQL.
CREATE TABLE Employee_Backup AS SELECT * FROM Employee;
To create a copy of the
Employee table, with selected columns and data, use
CREATE TABLE AS command, as shown below:
CREATE TABLE TempEmployee as (SELECT EmpId, FirstName, LastName FROM Employee);
To create the copy of the
Employee table, with just Structure and NO DATA, use the
CREATE TABLE AS command, as shown below:
CREATE TABLE Consultant AS SELECT * FROM Employee WHERE 1=2;
Above, the where clause
where 1=2 will not return any records and so the above command only creates a table without data.
Create a Copy of Table in MS SQL Server
SELECT * INTO command creates a copy of an existing table with structure and data.
SELECT * INTO Employee_backup FROM Employee
Table Design Guidelines
Before creating any table in the database, there is a need to design the tables that what kind of information will be stored in a table. What are columns needed, and what will be the datatypes of columns? What constraints will be applicable on different columns? What constraints should be applied on a table? What will be the relationship between the tables?
Here are some guidelines to consider while designing tables:
- Use abbreviated but meaningful names while defining table and column names.
- The maximum length allowed for a column name is 30 characters, and they should begin with the alphabet.
- Define appropriate Data Types for each column of the table.
- Try to keep only required columns in a table. The less important columns can be kept into some other detail table instead of storing them in the main table.
- Always try to put a column which can contain NULL values. That way, it will save storage space.