Difference between Primary key Unique Key in TERADATA

In Teradata, the difference between primary key and unique key constraints is quite similar to other relational database management systems like Oracle, Microsoft SQL Server, and MySQL. Let's delve into the specifics with examples:

1. Primary Key:

   - A primary key uniquely identifies each record in a table and ensures that the column(s) it is applied to does not contain duplicate or NULL values.

   - Each table can have only one primary key.

   - Teradata automatically enforces a unique index on the primary key column(s).

   - It's typically used for the main identifier of a table.

Example:

CREATE TABLE Employees (

    EmployeeID INT PRIMARY KEY,

    FirstName VARCHAR(50),

    LastName VARCHAR(50)

);

In this example, EmployeeID is the primary key, ensuring each employee has a unique identifier.

2. Unique Key:

   - A unique key constraint ensures that all values in a column or a set of columns are unique within the table. Unlike primary keys, unique keys can contain NULL values.

   - Multiple unique key constraints can exist within a single table.

   - Teradata also automatically creates a unique index on the unique key column(s).

   - It's used when you want to ensure uniqueness but don't need the primary key constraints.

Example:

CREATE TABLE Students (

    StudentID INT,

    StudentEmail VARCHAR(100) UNIQUE,

    FirstName VARCHAR(50),

    LastName VARCHAR(50)

);

In this example, StudentEmail is a unique key, ensuring that each email address stored in the table is unique, but it allows for NULL values.

In summary, the differences between primary key and unique key constraints in Teradata are similar to other RDBMS systems. Primary keys are stricter in disallowing NULL values and are typically used as the main identifier, while unique keys can allow NULLs and are used for other unique constraints.