Key in Oracle SQL

In Oracle SQL, keys are special columns or sets of columns that help uniquely identify rows in a table and establish relationships between tables. Here are the main types of keys:

1. Primary Key

A primary key uniquely identifies each row in a table. It must contain unique values and cannot contain NULL values. Each table can have only one primary key, which can consist of one or multiple columns (a composite primary key).

CREATE TABLE Employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(50) NOT NULL
);

Here, emp_id is the primary key, ensuring each employee has a unique ID.

1. Foreign Key

A foreign key is a column (or combination of columns) that creates a link between two tables by referencing a primary key in another table. It enforces referential integrity, ensuring values in the foreign key column exist in the referenced primary key column.

CREATE TABLE Departments (
dept_id NUMBER PRIMARY KEY,
dept_name VARCHAR2(50)
);

CREATE TABLE Employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(50),
dept_id NUMBER,
FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);

Here, dept_id in Employees is a foreign key referencing dept_id in Departments.

1. Unique Key

A unique key constraint ensures that all values in a column (or set of columns) are unique. Unlike a primary key, a table can have multiple unique keys, and unique key columns can contain NULL values.

CREATE TABLE Employees (
emp_id NUMBER PRIMARY KEY,
emp_email VARCHAR2(100) UNIQUE
);

Here, emp_email is a unique key, ensuring no duplicate email addresses, although it allows NULL.

1. Composite Key

A composite key is a primary or unique key made up of two or more columns. It is used when a single column isn’t enough to uniquely identify rows in a table.

CREATE TABLE ProjectAssignments (
emp_id NUMBER,
project_id NUMBER,
assignment_date DATE,
PRIMARY KEY (emp_id, project_id)
);

Here, (emp_id, project_id) together form a composite primary key, uniquely identifying each assignment.

1. Candidate Key

A candidate key is any column or set of columns that could serve as a primary key. Each table may have multiple candidate keys, but only one can be chosen as the primary key.

For example, in an Employees table:

emp_id and emp_email could both uniquely identify rows.

You might choose emp_id as the primary key, while emp_email remains a candidate key.

1. Alternate Key

An alternate key is any candidate key that is not chosen as the primary key.

In the example above, if emp_id is the primary key, then emp_email is considered an alternate key.

1. Super Key

A super key is any combination of columns that can uniquely identify rows in a table. A primary key, unique key, or a combination of these can be a super key. Super keys can contain additional columns that aren’t necessary for uniqueness.

For example:

In the Employees table, emp_id is a super key.

The combination (emp_id, emp_name) is also a super key, though it contains unnecessary information (emp_name).

These keys help enforce data integrity, establish relationships between tables, and ensure that each record in a database is unique and correctly associated.