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 🙂