A key is an attribute or set of attributes in a relation that uniquely identifies a tuple in a relation.
NOTE: All keys are super key. EXAMPLE:
|STUDENT ID||STUDENT ROLLNO||STUDENT NAME||STUDENT MARKS|
Suppose we have a table that holds all the students in a college, and that table is called students information. The table has columns called STUDENT ID, STUDENT ROLLNO, STUDENT NAME, and STUDENT MARKS. Every student has his/her own STUDENT ID, STUDENT ROLLNO, so that value is always unique in each and every row.
Candidate Key can be defined as a set of one or more fields/columns that can identify a record uniquely in a table like primary key or we can also say that other fields than a primary key which can become primary key and a table can have more than one candidate key. Each candidate key can work as primary key if required in any case.
For Example: Suppose a table consists of Employee data with fields Employee_Name, Employee_Address,Employee_Id , Employee_Designation. Employee_PANNo and Employee_PhoneNo in this table Employee_PhoneNo and Employee_PANNo are Candidate Keys as these two fields can also work as a candidate key.
Composite Key can be defined as a combination of more than one fields/columns of a table to uniquely identify the record. Fields which can be combined to make composite key can be a candidate, primary key.
Read More: Different Type of SQL Joins
For Example: Suppose a table consists of Employee data with fields Employee_Name, Employee_Address, Employee_Id , Employee_Designation, Employee_PANNO and Employee_PhoneNo in this table to build a composite key we combine Employee_Id and Employee_PhoneNo to fetch data from the table.
Foreign Key can be defined as a field/column in the Company table that is Primary key in Employee table. It can also accept multiple null values and duplicate values. This can be easily understood with the help of example given below.
For Example: We can have an Employee_Id column in the Company table which is pointing to Employee_Id column in an Employee table where it a primary key. So with the help of a foreign key, we can easily identify the data from tables.
Primary key can be defined as a set of one or more fields/columns of a table that uniquely identify a record in a database table. A record can be uniquely identified when the column which includes unique value like Employee_Id of an employee from an organization. It will not accept null values and duplicate values. Only one primary key can exist in a single table not more than one.
Example: Suppose a table consists of Employee data with fields Employee_Name, Employee_Address, Employee_Id and Employee_Designation so in this table only one field is there which is used to uniquely identify detail of Employee that is Employee_Id. Read More: Different Types of SQL Database Functions
The unique key can be defined as a set of one or more fields/columns of a table that have the capability to uniquely identify a record in the database table. We can have other fields also in a table beyond primary key which is also able to uniquely identify the record. It can accept only one null value and it cannot have duplicate values in it.
Example: Suppose a table consists of Employee data with fields Employee_Name, Employee_Address, Employee_Id, Employee_Designation and Employee_PhoneNo so in this table except for Employee_Id we also have an another field named Employee_PhoneNo which is can also be used to uniquely identify the record and can be termed as Unique Key.
A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.
Receive Latest Materials and Offers on SQL Server Course