PHP Create MySQL TABLE

The CREATE TABLE statement is used to create a new MySQL table.

MySQL CREATE TABLE Syntax

CREATE TABLE table_name(
   column_name data_type(data_size) column_constraint,
   column_name data_type(data_size) column_constraint,
   table_constraint(column_name)
);

The data type specifies which type of data the column can hold.For a complete reference of Data types, go to out Data types reference.
There are two basic types of constraints i.e. column_constraint(column constraint) and table_constraint(table constraint).The difference between column constraint and table constraint is that column constraint apply to individual column, whereas table constraints apply to group of one or more columns.
Here are some common constraints:

  • PRIMARY KEY: This constraint is similar to unique constraint except that only one column(or one group of columns) can be applied in this constraint.The PRIMARY KEY cannot allow NULL values.
  • DEFAULT: A default value can be specified using the DEFAULT clause.When a user doesn’t enter a value for a column(having default value),automatically default value is inserted in the field.
  • CHECK: This constraint limit values that can be inserted into a column of a table.
  • UNSIGNED:Used for number types data, limits the stored data to positive numbers and zero
  • NOT NULL: Each row must contain some value for that column,null value is not allowed.
  • UNIQUE:The UNIQUE constraint uniquely identifies each record in column of a database table.

Example of CREATE TABLE Statement

In this example we are creating a table named messages.

CREATE TABLE messages
(
   msg_id INT(11) NOT NULL AUTO_INCREMENT,
   message TEXT(400),
   time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
   uid INT(11) NOT NULL,
   status VARCHAR(2) DEFAULT '1',
   PRIMARY KEY(msg_id)
);

CHECK Constraint Example

In this example we will create a table named persons, and allow to register after an age of 18 years.

CREATE TABLE persons
(
    id INT(11) NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    age INT(3),
    CHECK (age>18)
);

UNIQUE Constraint Example

In this example we will create a table of users and the md5_hash will be unique.

CREATE TABLE persons
(
    id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    md5_hash VARCHAR(110) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    pwd VARCHAR(100) NOT NULL
);

Please don’t forget to share this article and subscribe us via email to get the latest articles on your email.

Shubham Kumar

Hey, I am Shubham and i love Blogging, Coding and exploring new things and obviously sharing my experience with you.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *