Question

what is super key,  candidate key, and primary key, and foreign key in terms of database? and plz provide some examples, thanks.

Answer 1

Super Key :-

A set of columns in database which can uniquely identify a record is called a super key. For eg.

Consider two tables

Student( id, name, class, rollNo, departmentId)

Department (departmentId, deptName)

Suppose name and class uniquely identify a student.

Also id uniquely identify a student

rollNo also uniquely identify a student.

So all these are super keys.

Candidate key:

A set of columns of minimal length which uniquely identifies a record in db is called Candidate key.

Eg.

In above example

rollNo uniquely identifies a record and is of length 1 as it is single column that uniquely identifies so ot is a candidate key

Primary key:

A primary key also uniquely identifies the record like candidate key. It is a column with unique and non null values.

Difference between candidate key and primary key is that a table can have multiple candidate keys, but single primary key.

So a primary key is a candidate key but all candidate keys are not primary keys.

eg. in above Student table,

there are two candidate keys id and rollNo

but primary key is id.

Foreign Key:

A column which refers another column from same table or a different table for its values is called foreign key. The possible values in a foreign key is the subset of values of referencing column.

eg. in a above example, Student table contains departmentId column which is a foreign key referencing departmentId column kf Department table.

The possible values in departmentId column of Student table is always subset of values in departmentId column in Department table