Today's Offer - SQL Server Certification Training - Enroll at Flat 10% Off.

- SQL Server Blogs -

Different Types of SQL Keys: Example and Uses

A key is an attribute or set of attributes in a relation that uniquely identifies a tuple in a relation.

Different types of keys:

  • Super Key
  • Candidate Key
  • Primary Key
  • Foreign Key
  • Alternate Key
  • Composite Key
  • Unique Key

SUPER KEY:

  • A set of attributes which contains a key.
  • A Super key is the most general type of a key.
  • There is no practical use of this key.
  • Super key can be Redundant.

NOTE: All keys are super key. EXAMPLE:

STUDENT ID STUDENT ROLLNO STUDENT NAME STUDENT MARKS
101 1 James 68
102 2 Sam 73
103 3 David 94
104 4 James 73

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:

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.

SQL Server Curriculum

Read: How to Compare MongoDB and DynamoDB?

 

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/COMPOUND 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.

Read: How to Restore a Database Backup from SQL Server Management Studio

FOREIGN KEY:

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.

SQL Server Quiz

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:

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

Read: Difference Between Clustered and Non-Clustered Index in the SQL

UNIQUE KEY:

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.

SQL Server training

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.

SQL Tutorial Overview


    Janbask Training

    JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.


Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

4 days 24 Nov 2019

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

5 days 25 Nov 2019

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

5 days 25 Nov 2019

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

6 days 26 Nov 2019

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

14 days 04 Dec 2019

Course for testing

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

34 days 24 Dec 2019

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

13 days 03 Dec 2019

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

5 days 25 Nov 2019

SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

8 days 28 Nov 2019

Comments

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews