Column definitions and constraints in MySQL

Introduction

Columns store data in tables. Constraints make sure the data is correct and follows rules.

When creating a new table to define what kind of data each column holds.

When you want to make sure a column always has a value (not empty).

When you want to make sure values in a column are unique.

When you want to link data between tables using keys.

When you want to limit the type or size of data stored in a column.

Syntax

MySQL

column_name data_type [constraint1] [constraint2] ...

Data types define what kind of data the column holds, like INT for numbers or VARCHAR for text.

Constraints are rules like NOT NULL (no empty values), UNIQUE (no duplicates), PRIMARY KEY (unique ID), FOREIGN KEY (link to another table), and DEFAULT (default value).

Examples

This defines a column named id that holds integers and is the primary key (unique identifier).

MySQL

id INT PRIMARY KEY

This defines a name column that holds text up to 50 characters and cannot be empty.

MySQL

name VARCHAR(50) NOT NULL

This defines an email column that holds text and must be unique (no duplicates).

MySQL

email VARCHAR(100) UNIQUE

This defines an age column that holds numbers and defaults to 18 if no value is given.

MySQL

age INT DEFAULT 18

Sample Program

This creates a table named Users with four columns. user_id is the unique ID, username and email must be unique and not empty, and age defaults to 18 if not provided.

MySQL

CREATE TABLE Users (
  user_id INT PRIMARY KEY,
  username VARCHAR(30) NOT NULL UNIQUE,
  email VARCHAR(50) NOT NULL UNIQUE,
  age INT DEFAULT 18
);

Important Notes

Always choose the right data type to save space and improve speed.

Use NOT NULL to avoid missing data.

Primary keys uniquely identify each row and help link tables.

Summary

Columns define the kind of data stored in a table.

Constraints enforce rules to keep data accurate and consistent.

Common constraints include PRIMARY KEY, NOT NULL, UNIQUE, and DEFAULT.