MySQL tutorial Day 4

Tables in MySQL

Tables are one of the most important components in a relational database. In MySQL, all data is stored inside tables, which consist of rows and columns.

Before storing data, we must first create a proper table structure with appropriate data types and validation/constraints.

In this tutorial, we will learn how to create tables in MySQL, understand table structure, and work with important constraints such as NOT NULL, UNIQUE, DEFAULT, and AUTO_INCREMENT. We will also learn how to view table definitions using the DESCRIBE command.

By the end of this lesson, you will be able to design and create professional database tables for real-world applications.

What is a Table in MySQL?

A table is a collection of related data organized into rows and columns.

For example, an employee table may contain:

| ------ | -------- | ------ | ---------- |

| 101 | Ali | 50000 | IT |

| 102 | Ahmed | 65000 | HR |

Here columns represent attributes or fields while Rows represent individual records.

Understanding Table Structure

Before creating a table, we must define:

Table name
Column names
Data types
Constraints

Below is an Example for Table Structure

Employee

---------

emp_id INT

emp_name VARCHAR(100)

salary DECIMAL(10,2)

hire_date DATE

In above table structure columns are emp_id, emp_name, salary and hire_date while INT, VARCHAR, DATE , FLOAT are data types.

CREATE TABLE Statement

The CREATE TABLE statement is used to create a new table in MySQL.

Syntax

CREATE TABLE table_name (

column1 datatype constraint,

column2 datatype constraint,

column3 datatype constraint

);

Creating Your First Table

Let us create an employee table.

CREATE TABLE employee (

emp_id INT,

emp_name VARCHAR(100),

salary DECIMAL(10,2),

hire_date DATE

);

Explanation

| Column | Description |

| --------- | ---------------------- |

| emp_id | Stores employee ID |

| emp_name | Stores employee name |

| salary | Stores employee salary |

| hire_date | Stores joining date |

Viewing Tables

To display all tables in the current database:

SHOW TABLES;

The above command will show a list of tables exists in current schema.

Viewing Table Structure

MySQL provides the DESCRIBE command to check table structure.

DESCRIBE table_name;

Let's check our created table structure

DESCRIBE employee;

Sample Output

| --------- | ------------- | ---- | --- | ------- | ----- |

| emp_id | int | YES | | NULL | |

Understanding Constraints

Constraints are rules or validation applied to table columns to maintain data integrity and accuracy. It will check automatically whenever any data insert, update or delete. The data can be add from database or any application or via any auto process

Constraints help:

prevent invalid data
maintain consistency
enforce business rules

Types of Constraints

We will see some of important constraints below

NOT NULL
UNIQUE
DEFAULT
AUTO_INCREMENT

NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot contain NULL values.

If a column is marked NOT NULL, the user must provide a value during insertion.

Syntax

column_name datatype NOT NULL

Example

CREATE TABLE employee (

emp_id INT NOT NULL,

emp_name VARCHAR(100) NOT NULL,

salary DECIMAL(10,2)

);

So we apply below rules in emp_id column by adding a constraint

* emp_id cannot be empty

* emp_name cannot be empty

* salary can contain NULL values

Now let's execute below statement to add new data in employee table and check the output

INSERT INTO employee

VALUES (101, 'Ali', 50000);

This works successfully because it fullfil the constraint restrictions

Now let's add an invalid data

INSERT INTO employee

VALUES (NULL, 'Ahmed', 60000);

MySQL will generate an error because emp_id is NOT NULL and we are trying to add blank in emp_id

# Why Use NOT NULL?

NOT NULL is useful for mandatory fields such as:

UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are different.

Duplicate values are not allowed.

column_name datatype UNIQUE

CREATE TABLE users (

user_id INT,

email VARCHAR(100) UNIQUE

);

Each email address must be unique.

Two users cannot have the same email.

INSERT INTO users

VALUES (1, 'ali@gmail.com');

Another Valid Insert

INSERT INTO users

VALUES (2, 'ahmed@gmail.com');

Invalid Insert

INSERT INTO users

VALUES (3, 'ali@gmail.com');

MySQL will reject this insertion because the email already exists.

Below are some key difference Difference Between PRIMARY KEY and UNIQUE

| Feature | PRIMARY KEY | UNIQUE |

| ---------------- | ----------- | ---------------- |

| Duplicate Values | Not Allowed | Not Allowed |

| NULL Values | Not Allowed | One NULL Allowed |

| Count Per Table | One | Multiple |

# Why Use UNIQUE?

UNIQUE is commonly used for:

* email addresses

* CNIC numbers

* phone numbers

* usernames

It prevents duplicate records.

DEFAULT Constraint

The DEFAULT constraint assigns a default value automatically if no value is provided.

column_name datatype DEFAULT value

CREATE TABLE employee (

emp_id INT,

emp_name VARCHAR(100),

city VARCHAR(50) DEFAULT 'Karachi'

);

If no city is specified, MySQL automatically inserts Karachi.

Insert Without City

INSERT INTO employee(emp_id, emp_name)

VALUES (101, 'Ali');

| emp_id | emp_name | city |

| ------ | -------- | ------- |

| 101 | Ali | Karachi |

Insert With City

INSERT INTO employee

VALUES (102, 'Ahmed', 'Lahore');

| emp_id | emp_name | city |

| ------ | -------- | ------ |

| 102 | Ahmed | Lahore |

Why Use DEFAULT?

DEFAULT is useful when:

Most records share a common value
optional values are needed
reducing manual input

AUTO_INCREMENT Constraint

AUTO_INCREMENT automatically generates sequential numbers.

Usually used with ID columns.

column_name INT AUTO_INCREMENT

AUTO_INCREMENT column should usually be:

PRIMARY KEY
UNIQUE

See below Example for auto increments

CREATE TABLE employee (

emp_id INT AUTO_INCREMENT PRIMARY KEY,

emp_name VARCHAR(100),

salary DECIMAL(10,2)

);

MySQL automatically generates emp_id values.

You do not need to insert them manually.

Let's add some data

INSERT INTO employee(emp_name, salary)

VALUES ('Ali', 50000);

As this column have an auto increment so no need to add this while inserting value

INSERT INTO employee(emp_name, salary)

VALUES ('Ahmed', 65000);

Below output can be extract via select SQL

| emp_id | emp_name | salary |

| ------ | -------- | ------ |

| 1 | Ali | 50000 |

| 2 | Ahmed | 65000 |

Benefits of AUTO_INCREMENT

Automatic numbering
avoids duplicate IDs
Simplifies insert operations
useful for primary keys

Combining Multiple Constraints

In real-world applications, multiple constraints are often used together.

CREATE TABLE employee (

emp_id INT AUTO_INCREMENT PRIMARY KEY,

emp_name VARCHAR(100) NOT NULL,

email VARCHAR(100) UNIQUE,

city VARCHAR(50) DEFAULT 'Karachi',

salary DECIMAL(10,2)

);

Explanation

| Column | Constraint |

| -------- | ---------------------------- |

| emp_id | AUTO_INCREMENT + PRIMARY KEY |

| emp_name | NOT NULL |

| email | UNIQUE |

| city | DEFAULT |

| salary | No constraint |

INSERT INTO employee(emp_name, email, salary)

VALUES ('Ali', 'ali@gmail.com', 50000);

| ------ | -------- | ------------------------------------- | ------- | ------ |

| 1 | Ali | [ali@gmail.com](mailto:ali@gmail.com) | Karachi | 50000 |

Best Practices for Table Creation

Use Meaningful Names

Good:

employee

customer

orders

Bad:

tbl1

test

abc

Use Proper Data Types

Examples:

* INT for numbers

* VARCHAR for text

* DATE for dates

Add Constraints Carefully

Use:

* NOT NULL for mandatory fields

* UNIQUE for unique values

* DEFAULT for common values

* AUTO_INCREMENT for IDs

Keep Table Structure Simple

Avoid:

* too many columns

* unnecessary duplicate data

Summary

In this lesson, we learned how to create tables in MySQL and define proper table structures using different constraints.

We covered:

* CREATE TABLE statement

* Table structure design

* NOT NULL constraint

* UNIQUE constraint

* DEFAULT constraint

* AUTO_INCREMENT constraint

* DESCRIBE command

Constraints are essential because they help maintain data accuracy, consistency, and integrity in databases.

A well-designed table st

Sructure is the foundation of every professional database application. Understanding these concepts is extremely important before moving to advanced SQL topics such as joins, relationships, procedures, and transactions.

Shahid Ahmed Patel

MySQL tutorial Day 4