MySQL : Create Table

by

Create Table in MySQL Database:

CREATE TABLE statement is use to create table in the database.

Here is the basic syntax to create table…

CREATE TABLE table_name ( column_name1 datatype constraints, column_name2 datatype constraints, column_name3 datatype constraints, … );

Example:

Here we are creating a table named students that stores student information like id, name, email, email_verified_at, password, role, remember_token, created_at, updated_at.

CREATE TABLE `users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `role` varchar(255) NOT NULL DEFAULT 'user' COMMENT 'Roles: user/admin',
  `remember_token` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ;

Explanation to Create Table in MySQL Database:

Columns:


id : Type: bigint(20) unsigned : Attributes: NOT NULL, AUTO_INCREMENT
This is the primary key of the table. It’s a BIGINT that can store large values of data , and AUTO_INCREMENT ensures that every new row gets a unique value.

name : Type: varchar(255) : Attributes: NOT NULL
This column holds the user’s name. It’s a variable-length string with a maximum length of 255 characters.

email: Type: varchar(255) : Attributes: NOT NULL
Description: This column stores the user’s email address. It’s also a variable-length string with a maximum length of 255 characters.

email_verified_at : Type: timestamp : Attributes: NULL DEFAULT NULL
This column holds the timestamp when the email was verified.


password: Type: varchar(255) Attributes: NOT NULL
Stores the hashed version of the user’s password.


role : Type: varchar(255) Attributes: NOT NULL DEFAULT ‘user’, COMMENT ‘Roles: user/admin’
This column holds the user’s role, which defaults to ‘user’. The comment indicates that the possible values can be user or admin.

remember_token : Type: varchar(100) Attributes: DEFAULT NULL
Stores a token used to remember a user’s session.


created_at : Type: timestamp Attributes: NULL DEFAULT NULL
Stores the timestamp of when the user record was created.


updated_at : Type: timestamp Attributes: NULL DEFAULT NULL
Stores the timestamp of the last time the user record was updated.


Primary Key:
PRIMARY KEY (id): This defines the id column as the table’s primary key, ensuring that each id is unique and serves as the main identifier for each row.


Leave a Comment