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 🙂
