SQL Keys

---

In SQL, keys are essential components used to identify and establish relationships between rows in database tables.

They play a critical role in maintaining data integrity and ensuring that the data in a database remains consistent.

Key types in SQL:

1. Primary Key (PK):

   • A primary key is a unique identifier for each row in a table.
   • It ensures that no two rows have the same values in the primary key column(s).
   • Primary keys are used to enforce data integrity and establish relationships between tables.
   • Typically, the primary key is indexed for performance reasons.

   Example:

   CREATE TABLE Students ( StudentID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50) );

2. Foreign Key (FK):

   • A foreign key is a column in a table that refers to the primary key of another table.
   • It establishes relationships between 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 INT PRIMARY KEY, CustomerID INT, OrderDate DATE, FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) );

3. Unique Key Constraint:

   • A unique key constraint ensures that values in a column (or columns) are unique, but unlike a primary key, it can allow NULL values.

   Example:

   CREATE TABLE Employees ( EmployeeID INT UNIQUE, FirstName VARCHAR(50), LastName VARCHAR(50) );

4. Alternate Key:

   • An alternate key is a candidate key that is not chosen as the primary key but still has a unique constraint.

5. 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 INT, ProductID INT, OrderDate DATE, PRIMARY KEY (OrderID, ProductID) );

6. Super Key:

   • A super key is a set of one or more columns that can be used to uniquely identify rows in a table.
   • It may contain more columns than required for a minimal unique identification, making it more than a candidate key.

These keys are fundamental in relational databases, ensuring data consistency and enabling the establishment of relationships between tables.

Properly defining and using keys is essential for designing a well-structured and efficient database.

Enroll Now

• SQL
• DBMS