MySql – Create Table

      No Comments on MySql – Create Table




In MySQL, the syntax for creating a simple table is given as below:


create table table_name(

   column_name1 column_type1,
   column_name2 column_type2,
   column_name3 column_type3  
);

Suppose , a table employee is to be created with columns as employee_id(Primary Key), employee_name, salary and joining_date,address_id(foreign key) which references id of Address table. Then, the tables can be created as below:

Query to create table Address:

create table Address(
   id int AUTO_INCREMENT PRIMARY KEY,
   city varchar(84),
   state varchar(128),
   country varchar(84)
);

Query to create table employee:

create table employee(
  employee_id int AUTO_INCREMENT PRIMARY KEY,
  employee_name varchar(256) NOT NULL,
  salary int,
  joining_date Date,
  address_id int references Address(id)
);

Different pre-defined Keywords which are used while creating a table:

I.) NOT NULL – This is used when you don’t want a column to contain a null value.

e.g.

create table employee(
  employee_id int AUTO_INCREMENT PRIMARY KEY,
  employee_name varchar(256) NOT NULL,
  salary int,
  joining_date Date,
  address_id references Address(id)

)

Here , employee_name column is declared as NOT NULL. So, this column can not contain any null values.

II.) AUTO_INCREMENT – This is used when it is required that the column value should get incremented automatically everytime an insertion happens in the table. Generally , this is used on id column.
e.g.

create table employee(
  employee_id int AUTO_INCREMENT PRIMARY KEY,
  employee_name varchar(256) NOT NULL,
  salary int,
  joining_date Date,
  address_id references Address(id)
)

Here, employee_id column values will get automatically incremented everytime an insertion in the table happens.

III.) PRIMARY KEY – This is used to declare any column as primary key column.

A table can have only one primary key. When a primary key constraint is declared for a table, a unique index is created on that column.

In the employee table here, employee_id is declared as Primary Key.

IV.) FOREIGN KEY – This is used to link a column of one table with the primary key of another table.

Here, in the employee table, address_id column references id column of Address.

Other ways to create primary key and foreign key:

create table employee(
  employee_id int AUTO_INCREMENT ,
  employee_name varchar(256) NOT NULL,
  salary int,
  joining_date Date,
  address_id int,
  PRIMARY KEY(employee_id),
  FOREIGN KEY(address_id) references Address(id)
)