Keys in Relational Model
Keys in Relational Model
On this page, we will learn about different types of keys in Relational Model.
- Keys ensure the data integrity and consistency and helps to access a record uniquely
- The relational Model has the following keys
Primary KeyForeign KeyCandidate KeyAlternate KeySuper Key
Primary Key
A primary key is a constraint in a table which uniquely identifies each row record in a database table by enabling one or more the column in the table as primary key.
Creating a primary key
A particular column is made as a primary key column by using the primary key keyword followed with the column name.
CREATE TABLE EMP ( ID INT , NAME VARCHAR (20) , AGE INT , COURSE VARCHAR(10) , PRIMARY KEY (ID) );
- Here we have used the primary key on ID column then ID column must contain unique values i.e one ID cannot be used for another student.
- If you try to enter duplicate value while inserting in the row you are displayed with an error
- Hence primary key will restrict you to maintain unique values and not null values in that particular column
Foreign Key
- The
foreign keyconstraint is a column or list of columns which points to the primary key column of another table - The main purpose of the foreign key is only those values are allowed in the present table that will match to the primary key column of another table.
Example to create a foreign key
Reference Table
CREATE TABLE CUSTOMERS1( ID INT , NAME VARCHAR (20) ,
COURSE VARCHAR(10) , PRIMARY KEY (ID) );
Child Table
CREATE TABLE CUSTOMERS2( ID INT , MARKS INT, REFERENCES CUSTOMERS1(ID) );
Candidate Key
Candidate keysare selected from the set of super keys, the only thing that you should remember while selecting candidate keys is, it should not have any redundant attitude- Definition of candidate key: Super key with no redundant attributes known as candidate key i.e should not contain any column that contains duplicate data.
Example for Candidate Key
- Consider the student table with columns [ID,NAME,PHONE]
- First, identify all the super keys present in the table, then eliminate the super keys that contain a column with duplicate data. Then the remaining superkeys that are left or nothing but candidate keys
- {Id}: ID column will contain all unique values hence ID column is a candidate key
- {phone}: As no two students have the same phone number, it is not redundant data column and hence phone column is a candidate key
- {Id, phone}: As both ID and phone are unique for all students this combination is a valid candidate key
- {Id, Name}: This combination is not a candidate key because name column may have duplicate values
- {Id, phone, Name}: This combination is not a candidate key because name column may have duplicate values
- {Name, Phone}: This combination is not a candidate key because name column may have duplicate values
Candidate keys available is the table student
- {Id}
- {phone}
- {Id, phone}
Alternate Key
Alternate keys are columns present in the table which are not selected as primary keys but still, they have all the capabilities to be used as a primary key is called alternate key.
Examples for an alternate key
- Example if a table student contain four columns [ID,NAME,PHONE,AGE ]
- Among these four columns ID is used as a primary key because no two students will have the same Id and capable of uniquely accessing a student record in the table
- In the same way phone attribute, no two students will have same phone number. Hence phone column is an alternate key because it is not been selected as a primary key, even if it is having the capability to be selected as a primary key
Super Key
- A
super keyis a group of single or multiple keys which uniquely identifies rows in a table. - A Super key may have additional attributes that are not needed for unique identification.
Example of the super key
Consider the student table with columns [ID,NAME,PHONE]
- [ID]: As no two students will have the same Id, it will help to uniquely access the student details, hence it is a super keyNow we will identify all the Super Keys present in the table that is the set of attributes that will help to uniquely access a record
- [NAME, ID]: Even if more than one student has the same name then the combination name will help to recognize the record, as student id will break the tie and this is combination is a super key
- [PHONE]: As no two students will have the same phone number, a phone number will help to uniquely access the student details and hence phone number is a super key
Prime Course Trailer
Related Banners
Get PrepInsta Prime & get Access to all 200+ courses offered by PrepInsta in One Subscription
Get over 200+ course One Subscription
Courses like AI/ML, Cloud Computing, Ethical Hacking, C, C++, Java, Python, DSA (All Languages), Competitive Coding (All Languages), TCS, Infosys, Wipro, Amazon, DBMS, SQL and others
Login/Signup to comment