Different types of keys | Primary Key and Foreign Key

One of the confusing topics in Database for beginners

• Primary Key
• Foreign Key
• Composite Key
• Candidate Key
• Alternate Key
• Super Key

Firstly, what are keys? Keys — uniquely identify records in a table and establish relationships between tables.

1. Primary Key

To distinguish one row from another, we need a unique attribute called a Primary Key.

2 main rule:

• Unique (no duplicate)
• NOT NULL (can’t be empty)

As you see at times names can be same with your groupmate but your id must be unique and to distinguish you from your friend we can’t use your names as PK. We use your IDs as PK because it is unique and doesn’t have any duplicate.

2. Foreign Key

Our second term is FK. It is used for making relations between tables. It is common attribute of the tables. I mean we look at both table and we pick an attribute which is located in both tables.

As you see both tables have the same attribute — StudentID and we’ll use this attribute to connect both tables.

And this picture shows that even though the tables foreign keys are the same columns but their column name can be different.

3. Composite Key

What if one column isn’t enough to identify a row?

Either of them is not enough alone because they are not unique separately. But their combination is unique and doesn’t have any duplicate.

Then we need both columns together. Think it as you choose 2 different subjects and thus to distinguish rows(tuple) we need to use composite key.

(StudenyID, CourseID)

Code sample:

CREATE TABLE Enrollments ( StudentID INT, CourseID INT,

PRIMARY KEY (StudentID, CourseID) );

4. Candidate Key

There are 2 candidate attributes exist as PK and we pick one of them. Both are unique.

5. Alternate Key

And we pick one of them in candidate key example. Alternate key is which we didn’t choose as PK in an example above. E.G.:

we chose FIN as PK and as you see StudentID is also unique. StudentId will be alternate key. In this example FIN -> PK , StudentID -> Alternate Key and both are candidate key.

6. Super Key

Think FIN is unique. Then all combinations with FIN are Super Keys.

(FIN) (FIN, Name) (FIN, Name, Age)