^ Click Here

Monday, June 25, 2012

Creating a table in MySQL

Frankly, lots of tutorials and you can easily gather info regarding this with google......

Still I am putting it simply with an example and then defining the parts in it so that it can be understood easily.......Suppose you have to create a table 'employee' in MySQL......

CREATE TABLE IF NOT EXISTS EMPLOYEE (
        id INT(20) NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,
 email VARCHAR(50) NOT NULL UNIQUE,
 PASSWORD VARCHAR(40),
 NAME VARCHAR(50),
 dob DATETIME DEFAULT NULL,
 sex ENUM("M","F","N") DEFAULT "N",
 STATUS ENUM("Single","Married","Complicated","Divorced","Relationship","Widowed","Engaged","Undisclosed") DEFAULT "Undisclosed",
 TYPE ENUM("Admin","Registered","Unregistered","Anonymous") DEFAULT "Registered",
 department_id INT(20),
 is_deleted ENUM("True","False") DEFAULT "False",
 created_date TIMESTAMP DEFAULT '0000-00-00 00:00:00',
 modified_date TIMESTAMP DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
 
 FOREIGN KEY (department_id) REFERENCES department(id)
) ENGINE = INNODB;

Now Let's see it line by line

CREATE TABLE IF NOT EXISTS EMPLOYEE
          - it is self-explanatory this only creates the table when there is no table in the database with the same table name, if we just use CREATE TABLE tableName and suppose we already have a table in the database with the same table name, it will throw an error. Hence using 'IF NOT EXISTS' we prevent that error and also its a nice practice.

id INT(20) NOT NULL AUTO_INCREMENT UNIQUE PRIMARY KEY,
             - id is the column name its of type int with range 20 i.e. an int with 20 digit. the id should not be null i.e. insuring a legitimate value,
AUTO_INCREMENT is assigning MySQL to take care of increasing the id automatically so that we don't have to insert id explicitly and it vanishes the chance of duplicacies of id as while manually entering you might try to enter an id which is already there.
UNIQUE is used to ascertain that for each employee there should be a unique id however with AUTO_INCREMENT we are already fulfilling that condition yet from a developer's point of view its desirable to apply such condition.
PRIMARY KEY is used to tell that this column would be used as the primary key for this table.

email VARCHAR(50) NOT NULL UNIQUE,
              -email is the name of the column. the type is varchar which is short form of variable character, variable character can contain number or special character like @ which is needed while storing email with domain name. NOT NULL again make sure that no employee data should be there without an email. UNIQUE is to make sure that all employee should have a unique email id, hence this constraint is legitimate and necessary here since it helps us in having an organized, transparent and clean data in the table.

dob DATETIME DEFAULT NULL,
             -dob is short for date of birth, the data type is DATETIME and if no value is specified while insertion, by default it would take NULL as value. note that even if we don't specify it will take null as a default.

sex ENUM("M","F","N") DEFAULT "N",
             -for column sex we are using ENUM which contains three values "M" short for Male, "F" short for Female and "N" for Not Provided. Default is "N" hence when sex is not specified for an employee, it would by default take "N" as value.

created_date TIMESTAMP DEFAULT '0000-00-00 00:00:00',
        -created date should be a time stamp type and if not specified it would take 0000-00-00 00:00:00 value. note that while inserting you can use sysdate() in the query as it is to put the timestamp while creating the row.

modified_date TIMESTAMP DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
            -modified_date same like created_date should be a timestamp type and has a default 0000-00-00 00:00:00 value. however it has an extra clause ON UPDATE CURRENT_TIMESTAMP. This take the tough task of automatically updating the timestamp whenever the row is updated so that we don't have to specify that in query. An important point to note that you can't have more than one instance where you can use CURRENT_TIMESTAMP i.e. only one column will have this clause of ON UPDATE CURRENT_TIMESTAMP.

FOREIGN KEY (department_id) REFERENCES department(id)
             -department_id is a column in the table with type int, with this line we are assuring that department_id is actually a FOREIGN KEY which references the 'id' column of 'department' table in the same database. Hence it is actually linking of the employee table with department table where each employee's department data can be retrieved by mapping the department_id with the id in department table. Not only it links and makes it more organized it makes sure that you don't delete a row from department table if that row id is being used in employee table for any employee, hence it helps us from committing accidental deletes and makes the tables clean and healthy.

 ENGINE = INNODB;
              -not much on this it specifies as which MySQL engine to use for this table. More on MySQL engines here.

Hope this helps.