PostgreSQL Keys

---

PostgreSQL, like many relational database management systems (RDBMS), supports various types of keys to maintain data integrity and establish relationships between tables.

The key types commonly used in PostgreSQL:

1. Primary Key (PK):

   • A primary key is a column or set of columns that uniquely identifies each row in a table.
   • It enforces the uniqueness constraint, ensuring that no two rows can have the same values in the primary key column(s).
   • Primary keys are used to create relationships between tables and are typically indexed for performance.

   Example:

   CREATE TABLE Customers ( CustomerID SERIAL PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50) );

2. Foreign Key (FK):

   • A foreign key is a column or set of columns in one table that refers to the primary key of another table.
   • It establishes a relationship between two tables, enforcing referential integrity.
   • The values in the foreign key column(s) must match values in the primary key column(s) of the referenced table.

   Example:

   CREATE TABLE Orders ( OrderID SERIAL PRIMARY KEY, CustomerID INT REFERENCES Customers(CustomerID), OrderDate DATE );

3. Unique Constraint:

   • PostgreSQL allows you to apply a unique constraint to one or more columns to ensure uniqueness.
   • Unlike the primary key, it allows NULL values in the unique constraint columns.

   Example:

   CREATE TABLE Employees ( EmployeeID SERIAL PRIMARY KEY, EmployeeCode VARCHAR(20) UNIQUE, FirstName VARCHAR(50), LastName VARCHAR(50) );

4. Composite Key:

   • A composite key is a key that consists of two or more columns, used together to uniquely identify rows.
   • It's often used when a single column cannot provide unique identification on its own.

   Example:

   CREATE TABLE Orders ( OrderID SERIAL, ProductID INT, OrderDate DATE, PRIMARY KEY (OrderID, ProductID) );

PostgreSQL supports these key types, and they play a crucial role in maintaining data integrity and defining relationships between tables in your database schema.

Enroll Now

• SQL
• DBMS