What are super, primary, candidate and foreign keys?

Question

Rocky Dada · Answer

In database design, there are several types of keys that are used to ensure data integrity and maintain relationships between tables. Here are the most common types of keys:

Super key: A super key is a set of one or more columns that can be used to uniquely identify a row in a table. A super key may include more columns than are necessary to uniquely identify a row.
Primary key: A primary key is a type of super key that uniquely identifies each row in a table. A primary key cannot contain null values and must be unique within the table. Each table can have only one primary key.
Candidate key: A candidate key is a set of one or more columns that could potentially serve as a primary key for a table. A candidate key must be unique and cannot contain null values. A table can have multiple candidate keys.
Foreign key: A foreign key is a column or a set of columns in one table that refers to the primary key of another table. A foreign key is used to establish a relationship between two tables. It ensures that the values in the referencing column(s) exist in the referenced table's primary key column(s). This helps to maintain referential integrity between related tables.

Here's an example to illustrate these concepts:

Let's say we have two tables, students and courses, and we want to establish a relationship between them. The students table has a primary key called student_id, which is a unique identifier for each student. The courses table has a primary key called course_id, which is a unique identifier for each course. We can establish a relationship between these tables by adding a foreign key to the courses table that refers to the student_id column in the students table. This foreign key ensures that each course is associated with a valid student, and maintains referential integrity between the two tables.

In summary, super keys are sets of one or more columns that can uniquely identify a row, primary keys are unique identifiers for each row in a table, candidate keys are sets of columns that could potentially serve as primary keys, and foreign keys are used to establish relationships between tables by referring to primary keys in other tables.

Anonymous User · Answer

The SuperKey is a set of attributes of a relation schema upon which all attributes of the schema are functionally dependent. There no two rows can have the same value of super key attributes. The Candidate key is a minimal superkey, i.e., no proper subset of Candidate key attributes can be a superkey. Whereas a Primary Key is one of the candidate keys. As we have understood that one of the candidate keys is chosen as the most important and we know the same as the primary key. There cannot be more that one primary keys in a table. The Foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. See this for an example.

interview

What are super, primary, candidate and foreign keys?

Can you answer this question?

2 Answers