Keys :key means which is an attribute(coloumn) or multiple attributes using
which any one retrive a single instance from the group instances
Keys:
1. To create reeationships between two tables
2. To maintain uniqueness in a table
3. To keep consistant and valid data in databases
4. Might help in fast data retrieval by facilitating indexes on coloums
Keys:
Empid Name Passport Dlno
No
1010 Sohel 12345 1
1011 Imran 23456 3
1013 Waseem 34567 4
RDBMS:
Key: to get row to identity single row
Key; key is an attribute which doesn’t allow duplicate values
Purpose; to retrive the single instance
Entities ---rows
Attributes ---coloumns
Entity instance –Row/Record
Alternate key –unique keys
Primary key –primary key
Candidate keys;
A candidate key is an attributes that indentifies or set of attributes that
indentity each record in a table or releation uniquely
Candidate key is a key of a table which can be selected as a primary key of the
table. A table can have multiple candidate keys, out of which one can be
selected as a primary key.
Example: Employee_Id, License_Number and Passport_Number
are candidate keys
A table could contain multiple candidate keys
This key cannot store a NULL
It must contain unique values
{ Attributes :
First name ; Last Name ; Age}
First Name Last Name Age
Smith Steve 33
Dhoni Mahendra 40
Smith Dyam 50
In this above example firstname is duplicate so we cant indentify the row
uniquely ,no key is candidate key here
Candidate key is unique
Key; key is an attribute which doesn’t allow duplicate values
Purpose; to retrive the single instance
Candidate keys; Empid,Passport no,,aadhar No ,License No
EMP ID License No Age Passport First Name Last name
NO
1 1111 33 P4564 Shaik Sohel
2 2222 33 P5322 Ravuri swetha
3 5555 36 P4677 Sunil Morh
Candidate keys: EMP ID , license , passport No
Primary key : empid
Alternative keys ;passportno ,dlno
Bank(account number)
Passport (passport no)
Primary key: Primary key in DBMS is a coloumn or group of coloumns in a table
that uniquely indentify every row in that table.
The primary key cannot be a duplicate meaning the same value cant appear
more than once in the table
A table cannot have more than one primary key
The value in a primary key coloumn can never be modified or updated if any
foregien key refers to that primary key
Primary key can be choosen depend upon project requirements
CREATE TABLE Customers (
CustomerID int PRIMARY KEY,
FirstName varchar(50),
LastName varchar(50),
Email varchar(100)
);
EMP ID License No Registration Passport First Name Last name
No NO
1 1111 L990 P4564 Shaik sohel
2 2222 L345 P5322 Ravuri swetha
3 5555 M236 P4677 Sunil Morh
Primary key : EMP ID
Alternative Keys:
licence No; Registration No; Passport No;
Ensures uniqueness
No null
Auto -creation of index(clustered)
One per table
Composite primary key
Not mandatory
#recommended
Unique key: Unique is a coloumn or group of coloumns in a table that uniquely
indentify every row in that table
The primary key cant be a duplicate meaning the same value cant appear more
than once in the table
A table can have more than one unique key
The unique key filed can allow only one null value in coloumn
CREATE TABLE Employees (
EmployeeID int,
FirstName varchar(50),
LastName varchar(50),
Email varchar(100),
CONSTRAINT UK_Employees_Email UNIQUE (Email)
);
EMP ID License No Registration Passport First Name Last name
No NO
1 1111 L990 P4564 Shaik sohel
2 2222 L345 P5322 Ravuri swetha
3 5555 M236 P4677 Sunil Morh
For alternative keys
Ensures uniqueness
Allows null
Only one null value
Auto creation of inedex(non clustered)
Many unique keys per table
What is the composite key?
Composite key is a combination of two or more coloumns that uniquely
indentify rows in a table
The combination of coloumns guarantess uniqueness ,though individually
uniqueness is not guaranteed. hence ,they are combined to uniquely indentify
records in a table
CREATE TABLE Orders (
OrderID int,
CustomerID int,
ProductID int,
OrderDate datetime,
CONSTRAINT PK_Orders PRIMARY KEY (OrderID, CustomerID, ProductID)
);
Teacher Table:
Teacher ID Qualification Id Qualification Age
1 MC MCA 30
2 BC BCA 30
3 MC MCA 30
2 MT Mtech 30
Teacher ID +Qualification
Composite unique keys
Pk vs uk
It doesnot allow null values = it allows only one null value
One pk per table = many uks per table
It creates clustered index = it creates non clustered index
Composite key; a key which has multiple attribute Ex; tower +no ; classsno
+sectno + rollno
Tower House Name Phone
no no no
A 1 S 11
A 2 F 23
One award id
For example,: In employee relations, we assume that an employee may be
assigned multiple roles, and an employee may work on multiple projects
simultaneously. So the primary key will be composed of all three attributes,
namely Emp_ID, Emp_role, and Proj_ID in combination. So these attributes act
as a composite key since the primary key comprises more than one attribute
What is the foreign key?
Foreign key is a coloumn that creates a relationship between two tables
The purpose of foreign keys is to maintain data integrity and allow navigation
between two different instances of an entity
It acts as across-reference between two tables as it refernces the primary key of
another table
DEPT table:
Dept code Dept name
001 Science
002 English
004 Computer
TEACHER TABLE:
Teacher ID Fname Lname
B002 David Warner
B017 Sara joseph
B009 john Son
Teacher id: Primary key
Deptcode: Foreign key
Skip to content
SQL Release Logo
Machine Learning, Data Science, & Data Engineering
Main Menu
Email *
Email *
Follow Us
Recent Posts
Optimize Spark dataframe write performance for JDBC Apr 30, 2023
Create [Link] file in Python automatically Feb 13, 2023
PII Data Identification using Presidio Open Source ML Library Feb 11, 2023
Difference between Hadoop 1.x, Hadoop 2.x and Hadoop 3.x Jan 30, 2023
Fill null with the next not null value – Spark Dataframe Dec 24, 2022
Yearly Archives
2023 (4)
2022 (15)
2021 (16)
2020 (14)
2019 (12)
2018 (15)
2017 (14)
2016 (23)
2015 (36)
2014 (47)
Monthly Archives
Monthly Archives
Select Month
Categories
Categories
Select Category
Available Tutorials
SQL Server Tutorial - Step By Step
Types of keys
In previous chapter, we had a good discussion on “Data Integrity” and in this
chapter “Types of Keys”, we are going to discuss Keys and their types in SQL
Server. Lets start this topic by defining keys”.
What is Key?
Keys are fields in a table which participate in below activities in RDBMS systems:
To create relationships between two tables.
To maintain uniqueness in a table.
To keep consistent and valid data in database.
Might help in fast data retrieval by facilitating indexes on column(s).
SQL Server supports various types of keys, which are listed below:
Candidate Key
Primary Key
Unique Key
Alternate Key
Composite Key
Super Key
Foreign Key
Before discussing each type in brief, have a look on the below image used as an
an example to define types of keys.
Types of keys
Types of keys
Lets discuss each type in detail:
Candidate Key
Candidate key is a key of a table which can be selected as a primary key of the
table. A table can have multiple candidate keys, out of which one can be
selected as a primary key.
Example: Employee_Id, License_Number and Passport_Number are candidate
keys
Primary Key
Primary key is a candidate key of the table selected to identify each record
uniquely in table. Primary key does not allow null value in the column and
keeps unique values throughout the column. In above example, Employee_Id is
a primary key of Employee table. In SQL Server, by default primary key creates a
clustered index on a heap tables (a table which does not have a clustered index
is known as a heap table). We can also define a nonclustered primary key on a
table by defining the type of index explicitly.
A table can have only one primary key and primary key can be defined in SQL
Server using below SQL statements:
CRETE TABLE statement (at the time of table creation) – In this case, system
defines the name of primary key
ALTER TABLE statement (using a primary key constraint) – User defines the
name of the primary key
Example: Employee_Id is a primary key of Employee table.
Unique Key
Unique key is similar to primary key and does not allow duplicate values in the
column. It has below differences in comparison of primary key:
It allows one null value in the column.
By default, it creates a nonclustered index on heap tables.
Alternate Key
Alternate key is a candidate key, currently not selected as primary key of the
table.
Example: License_Number and Passport_Number are alternate keys.
Super Key
Super key is a set of columns on which all columns of the table are functionally
dependent. It is a set of columns that uniquely identifies each row in a table.
Super key may hold some additional columns which are not strictly required to
uniquely identify each row. Primary key and candidate keys are minimal super
keys or you can say subset of super keys.
In above example, In Employee table, column Employee_Id is sufficient to
uniquely identify any row of the table, so that any set of column from Employee
table which contains Employee_Id is a super key for Employee Table.
For example: {Employee_Id}, {Employee_Id, Employee_Name}, {Employee_Id,
Employee_Name, Address} etc.
License_Number and Passport_Number columns can also identify any row of
the table uniquely. Any set of column which contains License_Number or
Passport_Number or Employee_Id is a super key of the table.
.