SQL Create Table

In the previous article, we have learned to create the databases. In this article, we will understand the SQL tables.

Basically, SQL tables are used to store the data, a table is a set of data elements (values) using a model of vertical columns (identifiable by name) and horizontal rows, the cell being the unit where a row and column intersect.


Syntax

Following is the Syntax to create a table in SQL

 CREATE TABLE table_name (
      column1_name data_type constraints,
      column2_name data_type constraints,
      ....
 );

A table is created by specifying the number of columns with their corresponding data type. Let's understand the above syntax.

  • The column1_name, column2_name are the name of columns in the table, remember that column name always comes first before the data_type.
  • The data_type is used to specify the type of value a column can hold. It can be any data type which is defined in SQL like (INT, VARCHAR, FLOAT, DATE etc)

Example

The following SQL statement creates a table named SampleDB

Try It
CREATE TABLE Customers
(
	Id INTEGER,
	FirstName NVARCHAR(255),
	LastName NVARCHAR(255),
	Email NVARCHAR(50)
)

In the above example Id column is of type INT which can hold an INTEGER value, wheres FirstName, LastName, and Email columns are of type VARCHAR. The Size of Columns like VARCHAR(255) specifies the number of characters a column can hold.


Constraint

We can add more functionality to the table columns while creating them by using constraints. Constraints are basically the rules related to the values allowed in columns. Following are some constraints.

  • PRIMARY KEY Constraint: The PRIMARY KEY constraint defines the particular column as the table's primary key. In a table, there can be only one primary key however it can be a combination of multiple columns.
  • NOT NULL Constraint: The NOT NULL constraint defines that the column can contain a NULL value.
  • AUTOINCREMENT Constraint: AUTOINCREMENT constraint is generally used with PRIMARY KEY constraint. AUTOINCREMENT automatically generates a new number into the specified column when a new record is inserted.

We will see more about the constraints in the next section.

Example

Try It
CREATE TABLE Employees
(
	Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	DepartmentId INTEGER NOT NULL,
	Name NVARCHAR(255),
	LastName NVARCHAR(255),
	Gender NVARCHAR(1),
	Phone NVARCHAR(15),
	Email NVARCHAR(50),
	Salary INTEGER
)