Tables and Columns

This document provides a comprehensive overview of tables and columns, fundamental components for organizing and storing data within the MSDN framework.

Understanding Tables

A table is a collection of related data held in a structured format within a database. It consists of columns (which define the attributes of the data) and rows (which represent individual records or entities). Tables are the primary means of organizing information.

• Structure: Tables have a defined schema that dictates the type and constraints of data each column can hold.
• Relationships: Tables can be related to each other through keys, enabling complex data queries and integrity.
• Primary Keys: A column or set of columns that uniquely identifies each row in a table.
• Foreign Keys: A column or set of columns that refers to the primary key of another table, establishing a link between them.

Understanding Columns

A column, also known as a field, represents a single attribute or characteristic of the data stored in a table. Each column has a name and a specific data type.

• Data Types: Specify the kind of data a column can store (e.g., integers, strings, dates, booleans). Common data types include:
  • INT: Whole numbers.
  • VARCHAR(n): Variable-length strings up to 'n' characters.
  • TEXT: Long strings.
  • DATE: Date values.
  • BOOLEAN: True or false values.
  • FLOAT: Floating-point numbers.
• Constraints: Rules applied to a column to ensure data integrity. Common constraints include:
  • NOT NULL: Ensures a column cannot have a NULL value.
  • UNIQUE: Ensures all values in a column are distinct.
  • DEFAULT: Sets a default value for the column if none is specified.
  • PRIMARY KEY: Marks the column as the primary identifier for the table.
  • FOREIGN KEY: Establishes a link to a primary key in another table.
• Naming Conventions: Column names should be descriptive and follow consistent naming conventions (e.g., camelCase or snake_case).

Creating Tables

Tables are typically created using SQL Data Definition Language (DDL). Below is an example of creating a simple Users table:

CREATE TABLE Users (
    UserID INT PRIMARY KEY AUTO_INCREMENT,
    Username VARCHAR(50) NOT NULL UNIQUE,
    Email VARCHAR(100) NOT NULL,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
);

Adding and Modifying Columns

Columns can be added to existing tables or modified using SQL commands.

Adding a Column

ALTER TABLE Users
ADD COLUMN LastLogin DATETIME NULL;

Modifying a Column

ALTER TABLE Users
MODIFY COLUMN Email VARCHAR(120) NOT NULL;

Best Practices

Further Reading

• Data Types in Detail
• Understanding Table Relationships
• SQL Commands Reference