MySQL Create Table Syntax Example

By | 2019-05-04T13:20:04+05:30 May 4th, 2019|mysql|

Here we will understand the MySQL create table syntax with constraints.

1. MySQL Create Table:

A simple create table example without applying any keys and constraints.

CREATE TABLE `employee` (
  `id` INT(11),
  `firstname` VARCHAR(150),
  `middlename` VARCHAR(150),
  `lastname` VARCHAR(150),
  `salary` DOUBLE,
  `designation` VARCHAR(100)
)ENGINE=InnoDB;

If we don’t provide any constraints to the columns SQL by default assigns DEFAULT NULL as default constraint.

2. MySQL create table with the primary key:

Designing an employee table with id as primary key and applying default null constraint to middlenamee.

CREATE TABLE `employee` (
  `id` INT(11) NOT NULL,
  `firstname` VARCHAR(150) NOT NULL,
  `middlename` VARCHAR(150) NULL DEFAULT NULL,
  `lastname` VARCHAR(150) NOT NULL,
  `salary` DOUBLE NOT NULL,
  `designation` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`)
)ENGINE=InnoDB;

2.1 Creating a primary key with default values:

2.1.1 The default value as an AUTO_INCREMENT.

CREATE TABLE `employee` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `firstname` VARCHAR(150) NOT NULL,
  `middlename` VARCHAR(150) NULL DEFAULT NULL,
  `lastname` VARCHAR(150) NOT NULL,
  `salary` DOUBLE NOT NULL,
  `designation` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`)
)ENGINE=InnoDB;

2.1.2 I am assigning 999 as a custom default value.

CREATE TABLE `employee` (
  `id` INT(11) NOT NULL DEFAULT '999',
  `firstname` VARCHAR(150) NOT NULL,
  `middlename` VARCHAR(150) NULL DEFAULT NULL,
  `lastname` VARCHAR(150) NOT NULL,
  `salary` DOUBLE NOT NULL,
  `designation` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`)
)ENGINE=InnoDB;

3. Verify:

Once you create a table, you can see it by using the show tables command like below.

mysql> show tables;
+---------------+
| Tables_in_otp |
+---------------+
| employee      |
+---------------+
1 row in set (0.00 sec)

To verify the structure of the table which you created, you can use the describe command.

mysql> describe employee;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id          | int(11)      | NO   | PRI | 999     |       |
| firstname   | varchar(150) | NO   |     | NULL    |       |
| middlename  | varchar(150) | YES  |     | NULL    |       |
| lastname    | varchar(150) | NO   |     | NULL    |       |
| salary      | double       | NO   |     | NULL    |       |
| designation | varchar(100) | NO   |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
6 rows in set (0.04 sec)

Done!

References:

Happy Learning 🙂

About the Author:

Hi Folks, you have reach this so far, that shows you like what you are learning. Then why don't you support us to improve for bettor tutorials by leaving your valuable comments and why not you keep in touch with us for latest updates on your favorite blog @ facebook , twitter , Or Google+ ,

Leave A Comment