How to Add a Column in Teradata: A Step-by-Step Guide

Introduction

Adding a column to an existing table in Teradata is a straightforward process. This guide will show you how to add a column, explain the syntax, and demonstrate common use cases with code examples and expected outputs.

Prerequisites

• A basic understanding of SQL
• Access to a Teradata database

Using ‘ALTER TABLE’

The ALTER TABLE command is used to modify an existing table structure. Here are some fundamental changes you can perform with ALTER TABLE:

• Add a new column
• Modify an existing column
• Drop a column
• Rename a column

For this tutorial, we’ll focus on adding a column.

Adding a Column - Basic Syntax

The basic syntax for adding a new column to a table is:

ALTER TABLE table_name
ADD column_name data_type [optional_constraint];

• table_name: The name of the table you wish to modify.
• column_name: The name of the new column to be added.
• data_type: The data type of the new column (e.g., INTEGER, VARCHAR, DATE).
• [optional_constraint]: Constraints like NOT NULL, UNIQUE, etc.

Practical Examples:

Prepare the Environment

First, let’s create a sample table for demonstration purposes.

CREATE TABLE Employees (
    EmployeeID INTEGER,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    HireDate DATE
);

Initial Data

INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate)
VALUES (1, 'John', 'Doe', '2022-01-15'),
       (2, 'Jane', 'Smith', '2021-06-30');

Expected Output:

Add Column with Data Type

Let’s start by adding a simple column, Email, to the Employees table.

ALTER TABLE Employees
ADD Email VARCHAR(100);

Verifying the Column Addition

To confirm that the column has been added, you can use the HELP TABLE command.

HELP TABLE Employees;

Expected Output:

Add Column with a Default Value

Sometimes, it’s useful to initialize a new column with a default value. Let’s add a column Status with a default value of ‘Active’.

ALTER TABLE Employees
ADD Status VARCHAR(20) DEFAULT 'Active';

Validate the Addition

Insert a new employee to see the default value.

INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate)
VALUES (3, 'Alice', 'Johnson', '2022-03-01');

Retrieve data to validate:

SELECT * FROM Employees;

Expected Output:

Add Column with Constraints

Now, let’s add a DepartmentID column which cannot be null.

ALTER TABLE Employees
ADD DepartmentID INTEGER NOT NULL;

Trying to insert a new record without DepartmentID will now throw an error:

INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate)
VALUES (4, 'Bob', 'Williams', '2022-02-20');  -- This will fail

Expected Output:

ERROR: Column 'DepartmentID' cannot be null

Adding Multiple Columns

Need to add more than one column? You can do that in a single ALTER TABLE statement.

ALTER TABLE Employees
ADD JobTitle VARCHAR(50),
ADD Salary INTEGER;

Verifying the Additions

HELP TABLE Employees;

Expected Output:

Conclusion

Adding columns to your Teradata tables is a simple operation that allows you to extend your database schema as needed. By following the steps and examples provided, while considering data types, constraints, and default values, you can ensure that your modifications are effective and accurate.

Happy querying!