Columns in Relational Databases

Columns are fundamental building blocks of tables in relational databases. Each column represents a specific attribute or characteristic of the data stored in the table. Understanding how to define, manipulate, and utilize columns is crucial for effective database design and management.

Defining Columns

When you create a table, you define its columns. Each column definition includes:

  • Name: A unique identifier for the column within the table.
  • Data Type: Specifies the type of data the column can hold (e.g., integer, text, date, boolean).
  • Constraints (Optional): Rules that govern the data allowed in the column (e.g., NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK).

Common Data Types

The specific data types available may vary slightly between different SQL database systems (like SQL Server, PostgreSQL, MySQL), but common ones include:

  • INT / INTEGER: For whole numbers.
  • VARCHAR(n) / NVARCHAR(n): For variable-length character strings, where 'n' is the maximum length.
  • DECIMAL(p,s) / NUMERIC(p,s): For exact numeric values with a fixed precision 'p' and scale 's'.
  • FLOAT / REAL: For approximate floating-point numbers.
  • DATE: For date values (year, month, day).
  • TIME: For time values (hour, minute, second).
  • DATETIME / TIMESTAMP: For date and time values combined.
  • BOOLEAN / BIT: For true/false values.
  • GUID / UNIQUEIDENTIFIER: For universally unique identifiers.

Column Constraints

Constraints enforce data integrity. Key constraints related to columns include:

PRIMARY KEY
Uniquely identifies each record in a table. A table can have only one primary key, and it cannot contain NULL values.
UNIQUE
Ensures that all values in a column are different. It allows NULL values (though typically only one NULL is permitted).
NOT NULL
Ensures that a column cannot have a NULL value. Every row must have a value for this column.
FOREIGN KEY
Creates a link between two tables. It enforces referential integrity by ensuring that a value in a column (or set of columns) in one table must match a value in a PRIMARY KEY or UNIQUE constraint in another table.
CHECK
Enforces domain integrity by limiting the range of values that can be placed in a column. It allows a BOOLEAN expression to be evaluated for each row.

Modifying Columns

You can alter table structure to add, modify, or delete columns using the ALTER TABLE statement. However, modifications to existing columns (especially those with data) can be complex and might require careful planning to avoid data loss or corruption.

Example: Creating a Table with Columns 

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL,
    Category VARCHAR(50),
    Price DECIMAL(10, 2) CHECK (Price >= 0),
    StockQuantity INT DEFAULT 0
);
Example: Adding a Column 

ALTER TABLE Products
ADD COLUMN Description TEXT;

Column Properties

Beyond their name and data type, columns can have other properties:

  • Default Value: A value automatically inserted if no value is provided during an insert operation.
  • Identity/Auto-increment: Automatically generates a unique sequential number for each new row, often used for primary keys.
  • Computed Columns: Columns whose values are calculated from other columns in the same table.

Properly defining and managing columns is fundamental to building robust and efficient relational databases. It ensures data accuracy, consistency, and facilitates effective data retrieval and manipulation.