June 4, 2024 By Matthew Rathbone

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:

EmployeeID FirstName LastName HireDate
1 John Doe 2022-01-15
2 Jane Smith 2021-06-30

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:

Column Name Type Comment
EmployeeID INTEGER  
FirstName VARCHAR(50)  
LastName VARCHAR(50)  
HireDate DATE  
Email VARCHAR(100)  

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:

EmployeeID FirstName LastName HireDate Email Status
1 John Doe 2022-01-15 NULL NULL
2 Jane Smith 2021-06-30 NULL NULL
3 Alice Johnson 2022-03-01 NULL Active

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:

Column Name Type Comment
EmployeeID INTEGER  
FirstName VARCHAR(50)  
LastName VARCHAR(50)  
HireDate DATE  
Email VARCHAR(100)  
Status VARCHAR(20)  
DepartmentID INTEGER  
JobTitle VARCHAR(50)  
Salary INTEGER  

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!

Beekeeper Studio Is A Free & Open Source Database GUI

Best SQL query & editor tool I have ever used. It provides everything I need to manage my database. - ⭐⭐⭐⭐⭐ Mit

Beekeeper Studio is fast, intuitive, and easy to use. Beekeeper supports loads of databases, and works great on Windows, Mac and Linux.

Beekeeper's Linux version is 100% full-featured, no cut corners, no feature compromises.