EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 600+ Courses All in One Bundle
  • Login

SQL Keys

By Priya PedamkarPriya Pedamkar

Home » Data Science » Data Science Tutorials » SQL Tutorial » SQL Keys

SQL Keys

Introduction to SQL Keys

In SQL, keys are the set of attributes that used to identify the specific row in a table and to find or create the relation between two or more tables i.e keys identify the rows by combining one or more columns. SQL provides super key, primary key, candidate key, alternate key, foreign key, compound key, composite key, and surrogate key. SQL keys use constraints to uniquely identify rows from karger data.

Syntax:

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

CREATE TABLE `customer` (
`cust_id` int(11) NOT NULL,
`cust_name` varchar(100) NOT NULL,
`cust_address` text NOT NULL,
`cust_aadhaar_number` varchar(50) DEFAULT NULL,
`cust_pan_number` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `customer` ADD PRIMARY KEY (`cust_id`);

In the above-given SQL query, we can see how a column ‘cust_id’ is set as a Primary Key.

Type of SQL Keys

Multiple types of Keys are supported by the SQL Server.

Type of SQL Keys

The following are the list of SQL Keys:

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

For Example 

Customer Table
cust_id cust_name cust_address cust_aadhaar_number cust_pan_number
100001 Sunil Kumar Noida 372464389211 ADSFS3456K
100002 Ankit Gupta Gr Noida 442289458453 CGHAD7583L
100003 Suresh Yadav New Delhi 878453444144 NMKRT2278O
100004 Nilam Singh Lucknow 227643441123 HFJFD3876U
100005 Amal Rawat Ghaziabad 932571156735 CBMVA9734A
100006 Harsh Saxena Kanpur 1453534363319 TRYUC2568H

Below given the “Order” table having the related data corresponding to the “cust_id” from the Customer Table.

Order Table
cust_id order_month_year order_amount
100001 2019 – Jan $100,000
100002 2019 – Jan $120,000
100003 2019 – Jan $100,000
100004 2019 – Jan $110,000
100001 2019 – Feb $105,000
100002 2019 – Feb $125,000

Now, we will go through one by one on each of the Key:

1. Primary Key

Primary Key is a field that can be used to identify all the tuples uniquely in the database. Only one of the columns can be declared as a primary key. A Primary Key can not have a NULL value.

Example: In the above given relational table, “cust_id” is the Primary Key as it can identify all the row uniquely from the table.

2. Unique Key

Unique Key can be a field or set of fields that can be used to uniquely identify the tuple from the database. One or more fields can be declared as a unique Key. The unique Key column can also hold the NULL value. Use of Unique Key improves the performance of data retrieval. It makes searching for records from the database much more faster & efficient.

Example: In the above given relational table, “cust_aadhaar_number”, “cust_pan_number” are the Unique Key as it can allow one value as a NULL in the column

3. Candidate Key

Candidate Key can be a column or group of columns that can qualify for the Unique Key. Every table has at least one Candidate Key. A table may have one or more Candidate Key. Each Candidate Key can work as a Primary Key if required in certain scenarios.

Example: In the above given relational table, “cust_id”, “cust_aadhaar_number”, “cust_pan_number” are the Candidate Key as it can identify all the row uniquely from the table. These columns also qualify the criteria to be a Primary Key.

 4. Alternate Key

Alternate Key is that Key which can be used as a Primary Key if required. Alternate Key also qualifies to be a Primary Key but for the time being, It is not the Primary Key.

Popular Course in this category
Sale
SQL Training Program (7 Courses, 8+ Projects)7 Online Courses | 8 Hands-on Projects | 73+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (9,309 ratings)
Course Price

View Course

Related Courses
JDBC Training (6 Courses, 7+ Projects)PHP Training (5 Courses, 3 Project)Windows 10 Training (4 Courses, 4+ Projects)PL SQL Training (4 Courses, 2+ Projects)Oracle Training (14 Courses, 8+ Projects)

Example: In the above given relational table, “cust_aadhaar_number”, “cust_pan_number” are the Alternate Key as both of the columns can be a Primary Key but not yet selected for the Primary Key.

5. Composite Key

Composite Key is also known as Compound Key / Concatenated Key. Composite Key refers to a group of two or more columns that can be used to identify a tuple from the table uniquely. A group of the column in combination with each other can identify a row uniquely but a single column of that group doesn’t promise to identify the row uniquely.

Example: In the above given relational table i.e. Order Table, “cust_id”, “order_month_year” group of these columns used in combination to identify the tuple uniquely in the Order Table. The individual column of this table is not able to identify the tuple uniquely from the Order table.

6. Super Key

Super Key is a combination of columns, each column of the table remains dependent on it. Super Key may have some more columns in the group which may or may not be necessary to identify the tuple uniquely from the table. Candidate Key is the subset of the Super Key. Candidate Key is also known as minimal Super Key.

Example: In the above given relational table, Primary Key, Candidate Key & Unique Key is the Super Key. As a single column of Customer Table i.e ‘cust_id’ is sufficient to identify the tuples uniquely from the table. Any set of the column which contains ‘cust_aadhaar_number’, ‘cust_pan_number’ is a Super Key.

7. Foreign Key

A foreign key is a column which is known as Primary Key in the other table i.e. A Primary Key in a table can be referred to as a Foreign Key in another table. Foreign Key may have duplicate & NULL values if it is defined to accept NULL values.

Example: In the above given relational table, ‘cust_id’ is Primary Key in the Customer table but ‘cust_id’ in the Order table known as a ‘Foreign Key’. Foreign Key in a table always becomes the Primary Key on the other table.

Foreign Key

The above-given picture displays how each column is shown as a Key according to their qualification to identify the tuples uniquely from the table. Screenshot summarizes all the Key through the use of the relational table.

Conclusion

SQL Keys is one of the attributes of the relational database. which plays important roles to establish a relationship between two or more tables. It also helps queries to execute faster i.e. retrieval of the records from the database becomes much faster by using Keys. Keys also set the different constraint to uniquely identify the tuples from the large data.

Recommended Articles

This is a guide to SQL Keys. Here we discuss the introduction to SQL Keys and 7 different types with the appropriate example in detail. You may also look at the following article –

  1. Distinct Keyword in SQL
  2. Cursors in SQL
  3. Foreign Key in SQL
  4. Transactions in SQL

SQL Training Program (7 Courses, 8+ Projects)

7 Online Courses

8 Hands-on Projects

73+ Hours

Verifiable Certificate of Completion

Lifetime Access

Learn More

1 Shares
Share
Tweet
Share
Primary Sidebar
SQL Tutorial
  • Keys
    • SQL Keys
    • Primary Key in SQL
    • Foreign Key in SQL
    • Unique Key in SQL
    • Alternate Key in SQL
    • SQL Super Key
  • Basic
    • What is SQL
    • Careers in SQL
    • Careers in SQL Server
    • IS SQL Microsoft?
    • SQL Management Tools
    • What is SQL Developer
    • Uses of SQL
    • How to Install SQL Server
    • What is SQL Server
    • SQL Server Versions
    • SQL Case Insensitive
    • SQL Expressions
    • Database in SQL
    • SQL Data Types
    • SQL Keywords
    • Composite Key in SQL
    • SQL WAITFOR
    • SQL Constraints
    • Transactions in SQL
    • First Normal Form
    • SQL Server Data Types
    • SQL Administration
    • SQL Variables
    • SQL Enum
    • SQL GROUP BY WHERE
    • SQL ROW
    • SQL EXECUTE
    • SQL EXCLUDE
    • SQL Performance Tuning
    • SQL UUID
    • Begin SQL
    • SQL Update Join
    • Cheat sheet SQL
  • Operators
    • SQL Operators
    • SQL Arithmetic Operators
    • SQL Logical Operators
    • SQL String Operators
    • Ternary Operator in SQL
  • Commands
    • SQL Commands
    • sqlplus set commands
    • SQL Alter Command
    • SQL Commands Update
    • SQL DML Commands
    • SQL DDL Commands
    • FETCH in SQL
  • Clause
    • SQL Clauses
    • SQL IN Operator
    • SQL LIKE Clause
    • SQL NOT Operator
    • SQL Minus
    • SQL WHERE Clause
    • SQL with Clause
    • SQL HAVING Clause
    • GROUP BY clause in SQL
    • SQL GROUP BY DAY
    • ORDER BY Clause in SQL
    • SQL ORDER BY CASE
    • SQL ORDER BY DESC
    • SQL ORDER BY DATE
    • SQL ORDER BY Alphabetical
    • SQL ORDER BY Ascending
    • SQL Order by Count
    • SQL GROUP BY Month
    • SQL GROUP BY Multiple Columns
    • SQL GROUPING SETS
  • Queries
    • SQL Insert Query
    • SQL SELECT Query
    • SQL SELECT RANDOM
    • SQL Except Select
    • SQL Subquery
    • SQL SELECT DISTINCT
    • SQL WITH AS Statement
  • Functions
    • SQL Date Function
    • SQL String Functions
    • SQL Compare String
    • Timestamp to Date in SQL
    • SQL Window Functions
    • SQL CONCAT
    • SQL ALTER TABLE
    • SQL MOD()
    • SQL Timestamp
    • SQL TO_DATE()
    • SQL DATEADD()
    • SQL DATEDIFF()
    • SQL HOUR()
    • SQLite?functions
    • ANY in SQL
    • LIKE Query in SQL
    • SQL NOT NULL
    • SQL NOT IN
    • SQL MAX()
    • SQL MIN()
    • SQL SUM()
    • SQL COUNT
    • SQL identity
    • SQL DELETE Trigger
    • SQL Declare Variable
    • SQL Text Search
    • SQL COUNT DISTINCT
    • SQL TEXT
    • SQL Limit Order By
    • BETWEEN in SQL
    • LTRIM() in SQL
    • TOP in SQL
    • SQL Select Top
    • Merge SQL
    • SQL TRUNCATE()
    • SQL UNION
    • SQL ALL
    • SQL INTERSECT
    • SQL Alias
    • SQL Server Substring
    • CUBE in SQL
    • SQL RANK()
    • SQL MOD()
    • SQL CTE
    • SQL LAG()
    • SQL MID
    • SQL avg()
    • SQL WEEK
    • SQL DELETE
    • SQL DATEPART()
    • SQL DECODE()
    • SQL DENSE_RANK()
    • SQL NTILE()
    • SQL NULLIF()
    • SQL Stuff
    • SQL Ceiling
    • SQL EXISTS
    • SQL LEAD()
    • SQL COALESCE
    • SQL BLOB
    • SQL ROW_NUMBER
    • SQL Server Replace
    • SQL Server Permission
    • T-SQL INSERT
    • T-SQL Stuff
    • T-SQL ADD Column
    • SQL Ranking Function
  • Joins
    • Join Query in SQL
    • Types of Joins in SQL
    • Types of Joins in SQL Server
    • SQL Inner Join
    • SQL Join Two Tables
    • SQL Delete Join
    • SQL Left Join
    • LEFT OUTER JOIN in SQL
    • SQL Right Join
    • SQL Cross Join
    • SQL Outer Join
    • SQL Full Join
    • SQL Self Join
    • Natural Join SQL
    • SQL Multiple Join
  • Advanced
    • SQL Wildcards
    • SQLPlus spool
    • SQL Schema
    • Comparison Operators in SQL
    • SQL_plus
    • SQL Formatter
    • SQL LEFT INNER JOIN
    • SQL Plus Command
    • SQLPlus not found
    • SQL Injection Attack
    • Aggregate Functions in SQL
    • SQL REVOKE
    • SQL Select Distinct Count
    • IF ELSE Statement in SQL
    • SQL CASE Statement
    • SQL While Loop
    • SQL BIGINT
    • SQL Crosstab
    • SQL Wildcard Character
    • SQLAlchemy Filter
    • SQLAlchemy SQLite
    • SQLAlchemy DateTime
    • SQLAlchemy create_engine
    • SQL INSTR()
    • SQL now
    • SQL synonyms
    • SQLite?export to csv
    • What is Procedure in SQL
    • Stored Procedure in SQL?
    • SQL Server Constraints
    • SQL DELETE ROW
    • Column in SQL
    • Table in SQL
    • SQL Virtual Table
    • SQL Merge Two Tables
    • SQL Table Partitioning
    • SQL Temporary Table
    • SQL Clone Table
    • SQL Rename Table
    • SQL LOCK TABLE
    • SQL Clear Table
    • SQL DESCRIBE TABLE
    • SQL Mapping
    • Cursors in SQL
    • AND in SQL
    • Wildcard in SQL
    • SQL FETCH NEXT
    • SQL Views
    • SQL Delete View
    • Triggers in SQL
    • SQL UPDATE Trigger
    • SQL AFTER UPDATE Trigger
    • SQL Update Statement
    • SQL DROP TRIGGER
    • Types of SQL Views
    • SQL Port
    • SQL Clustered Index
    • SQL COMMIT
    • Distinct Keyword in SQL
    • PARTITION BY in SQL
    • SQL Set Operators
    • SQL UNION ALL
    • Metadata in SQL
    • SQL Bulk Insert
    • Array in SQL
    • SQL REGEXP
    • JSON in SQL
    • SQL For loop
    • EXPLAIN in SQL
    • ROLLUP in SQL
    • Escape Character SQL
    • SQL Cluster
    • SQL Backup
    • SQL Pattern Matching
    • SQL Users
    • ISNULL SQL Server
    • SQL pivot
    • SQL Import CSV
    • SQL if then else
    • SQL ignore-case
    • SQL Matches
    • SQL Search String
    • SQL Column Alias
    • SQL extensions
    • SQL Substring Function
    • Charindex SQL
  • NoSQ
    • NoSQL Databases List
    • NoSQL Injection
    • NoSQL vs SQL Databases
  • Interview Questions
    • SQL Interview Questions
    • Advance SQL Interview Questions
    • SQL Joins Interview Questions
    • SQL Server Interview Questions
    • SQL Current Month

Related Courses

JDBC Training Course

PHP course

Windows 10 Training

SQL Course Training

PL/SQL Certification Courses

Oracle Certification Courses

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Java Tutorials
  • Python Tutorials
  • All Tutorials
Certification Courses
  • All Courses
  • Software Development Course - All in One Bundle
  • Become a Python Developer
  • Java Course
  • Become a Selenium Automation Tester
  • Become an IoT Developer
  • ASP.NET Course
  • VB.NET Course
  • PHP Course

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

Special Offer - SQL Training Program (7 Courses, 8+ Projects) Learn More