SQL - CREATE TABLE

Creation of a basic table in an SQL Scheme requires Name of the Table, Name(s) of Attribute(s), it's Data Types and size, Various Constraints, etc.

The basic syntax of CREATE TABLE statement is


CREATE TABLE Table_Name(
Column_Name1 Data_Type (Column_Width)[Constraints],
Column_Name2 Data_Type (Column_Width)[Constraints],
--------------------------------------------------,
);

where table name assigns the name of the table, column name defines the name of the field, data type specifies the data type for the field and column width allocates specified size to the field.
Note:

  • Table name should start with an alphabet.
  • In table name, blank spaces and single quotes are not allowed.
  • Reserve words of that DBMS can not be used as table name.
  • Proper data types and size should be specified.
  • Unique column name should be specified.
example:

MariaDB [testdatabase]> create table employee (empno integer(6) primary key,
    -> name varchar(30) not null,
    -> designation varchar(20),
    -> department varchar(20),
    -> basicpay decimal(8,2),
    -> da decimal(8,2),
    -> total decimal(8,2));
Query OK, 0 rows affected (1.89 sec)
MariaDB [testdatabase]>

The command can be written alternatively as,
MariaDB [testdatabase]> create table employee (empno integer(6),
    -> name varchar(30) not null,
    -> designation varchar(20),
    -> department varchar(20),
    -> basicpay decimal(8,2),
    -> da decimal(8,2),
    -> total decimal(8,2),
    -> primary key(empno));
Query OK, 0 rows affected (1.89 sec)
MariaDB [testdatabase]>

Here,

Field Attribute NOT NULL is being used because we do not want this field to be NULL. So, if a user will try to create a record with a NULL value, then MySQL will raise an error.

Keyword PRIMARY KEY is used to define a column as a primary key. You can use multiple columns separated by a comma to define a primary key.