Create Tables In MySQL

In MySQL, The CREATE TABLE statement defines and creates a new table. This statement defines the table structure, table name, column names, data types, and constraints.

Steps Involved in Creating Table:

Define the Table Name: Choose a meaningful name for the table.
Specifying Columns and their Types: Define each column's name, data type, and constraints for column.
Adding Constraints: Use constraints to enforce data integrity (e.g., primary keys, foreign keys, unique constraints).
Considering Relationships: Use foreign keys to link related tables or its data dependent on other tables as well.

Syntax of CREATE TABLE Statement In MySQL:

CREATE TABLE tableName (
    column1 datatype,
    column2 datatype,
    PRIMARY KEY (one_or_more_columns)
);

tableName: This is the name of the table you want to create.
column1, column2: These are the names of the columns in the table.
datatype: Specifies the data type of each column.
PRIMARY KEY: Specifies the primary key for the table, which is a unique identifier for each row. It can consist of one or more columns.

Create Table Command In MySQL:

Example:

CREATE TABLE products (
    product_code INT PRIMARY KEY,
    product_name VARCHAR(50),
    product_price VARCHAR(50),
    product_upload_date DATE,
    FOREIGN KEY (catalog_id) REFERENCES catalogs(catalog_id)
);

The table named is "products".
It has columns: "product_code", "product_name", "product_price", and "product_upload_date".
The data types are specified for each column (INT for integers, VARCHAR(50) for variable-length character strings, and DATE for dates).
The PRIMARY KEY constraint is set on the "product_code" column.
The FOREIGN KEY constraint is set on the related tables, we can add foreign keys to maintain referential integrity.

Adding Foreign Key In MySQL:

If we have related tables, we can add foreign keys to maintain referential integrity. For example, suppose we have a `catalog` table.

Catalog Table for Reference Mapping In MySQL:

CREATE TABLE catalog (
    catalog_id INT PRIMARY KEY,
    catalog_name VARCHAR(50) NOT NULL,
    catalog_description VARCHAR(100) NOT NULL,
);

The table named is "catalog".
It has columns: "catalog_id", "catalog_name", and "catalog_description".
It has PRIMARY KEY constraint set on the "catalog_id" column and `catalog_id` is foreign key in `products` table.

<-- Drop Database

Alter Table -->