Non-Key Attributes in DBMS
The primary key is an attribute that uniquely identifies any record in a table. The values inside the primary cannot be repeated. This is the main feature of a primary key. The attribute that does not identify any record uniquely is called a Non-key attribute. Or the attributes that are not key or part of the key attributes are known as non-key attributes. Non-key attributes can store as many as the values repeatedly.
When employees join a company or an organization, the company does not expect that all the employees they hire have a unique first name and last name; both first and last names can be the same. So, the name cannot become the primary key and hence become a non-key attribute in a table or database.
For example, in a Company type table, the database can have multiple instances for the customer name, which shows that the "customer name" cannot become unique as more than 1 person has the same. Thus, the customer name in this example falls under the non-key attribute.
Although, names chosen for any profile or designation in an organization can be key attributes as the way a company will choose- any company will not choose and give two different designations and profile the same name.
Example to understand non-key attributes deeply
Below we took two schemas or tables and their attributes, where primary attributes are underlined, and non-key attributes are non-underlined.
- EMPLOYEE (Emp_id, Name, DOB, Address)
- DETAILS (Emp_id, Emp_Enrolment, Salary, Department)
In the schema EMPLOYEE, the attribute Emp_id is the primary key (prime attribute), meaning the employees' Ids cannot be the same. And all the other attributes are non-key attributes means other attributes can have the same value or data.
In schema DETAILS, Emp_id and Emp_Enrolment are two primary keys (prime attributes). All the other attributes are non-key attributes. In this case, Emp_id individually is one prime attribute, and Emp_Enrolment is another prime attribute.
Why are they called Non-key attributes?
They are non-key attributes because they cannot uniquely identify any records of the table.
For example, look at the following queries;
SELECT * from EMPLOYEE where Emp_id = ‘2087’;
SELECT * from EMPLOYEE where Name = ‘Abhishek’;
Here, Emp_id is the primary key of the Employee table, so this query will return only one record if 2087 is present in the emp_id section of Employee; otherwise, it will return zero. On the other hand, the name is not a primary key. Hence, the second query will return all the records that stored 'Abhishek' as a name. That's why they are called non-key attributes.