SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO Values SQL INSERT INTO SELECT SQL Insert multiple rows

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS Difference between Delete, Drop and Truncate in SQL

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate Update Query in SQL Grant Command in SQL SQL SET Keyword SQL Order BY LIMIT SQL Order BY RANDOM

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL How index works in SQL How to calculate age from Date of Birth in SQL How to Rename Column name in SQL what-are-single-row-and-multiple-row-subqueries">What are single row and multiple row subqueries?

SQL KEYS

SQL KEYS are single or multiple attributes used to get data from the table according to the requirement or condition. They can also be used to set up relationships amongst various tables. There are several types of SQL KEYS, which are:

  1. Super key
  2. Candidate key
  3. Primary key
  4. Alternate key
  5. Composite key
  6. Unique key
  7. Foreign key

1. SUPER KEY

A SUPER KEY is a combination of one or multiple columns in a table in the database, which help in the unique identification of each row in the table. It is a group of one or several keys.

Example:

EmpSSNEmpNumEmpName
1254654TAE03Harry
2165465TAE04Ron
2154864TAE05Dobby

In the above example, EmpSSN and EmpNum are SUPER KEYS because they help in identifying each row in the table uniquely.

2. CANDIDATE KEY

CANDIDATE KEY is also a set of columns or attributes that help identify each row in the table separately. A CANDIDATE KEY can be defined as a SUPER KEY having no matching attributes. It can be demonstrated as a sub-set of SUPER KEY. Several candidate keys can be there in a table.

Example:

StuIDRollFirstNameLastNameEmail
0142HarryKane[email protected]
0243RonWiesley[email protected]
0344DobbyWright[email protected]

StuID, Roll and Email are CANDIDATE KEYS in the above table because they help to identify each row uniquely.

3. PRIMARY KEY

PRIMARY KEY is an attribute or a group of attributes that help in identifying individual rows distinctly. There cannot be the exact value of the PRIMARY KEY more than once in the table. A PRIMARY KEY can be expressed as a sub-set of a CANDIDATE KEY. There cannot be Multiple PRIMARY KEYS in a table.

Properties of a PRIMARY KEY:

  • There cannot be duplicate values of PRIMARY KEY in the table.
  • PRIMARY KEY cannot contain null values.
  • The value of a PRIMARY KEY should not be changed with time.
  • Each individual row in the table should contain a PRIMARY KEY.

Example:

StuIDRollFirstNameLastNameEmail
0142HarryKane[email protected]
0243RonWiesley[email protected]
0344DobbyWright[email protected]

StuID is the primary key in the above example since it can uniquely identify each record in the table.

4. ALTERNATE KEY

ALTERNATE KEY helps in identifying the records in the table distinctly. There can be several columns in a table that can identify individual rows in the table separately. Out of those attributes, only one attribute is chosen as the PRIMARY KEY. The rest of the attributes become ALTERNATE KEYS.

Example:

StuIDRollFirstNameLastNameEmail
0142HarryKane[email protected]
0243RonWiesley[email protected]
0344DobbyWright[email protected]

In the above example, Roll and Email are ALTERNATE KEYS.

The following representation will help understand CANDIDATE KEY, PRIMARY KEY, and ALTERNATE KEY in a better way.

5. COMPOSITE KEY

COMPOSITE KEY is a merger of multiple columns that help in identifying each row distinctly. This distinctness is guaranteed only when the columns are combined. When the columns are taken individually, it does not promise distinctiveness. A PRIMARY KEY which is made of multiple attributes, is defined as a COMPOSITE KEY.

Example:

OrderNoProductIDProductNameQuantity
A0015624185LCD1
A0013216546Printer2
A0013516527Mouse3
A0029816846Keypad1
A0037160354USB5

In the above example, OrderNo and ProductID combined to form the COMPOSITE KEY. They individually cannot identify each row in the table uniquely, but when they are combined, they can identify each record in the table uniquely.

6. UNIQUE KEY

UNIQUE KEY can also identify each row in a table uniquely like a PRIMARY KEY. But, unlike a PRIMARY KEY, a UNIQUE KEY can have only a single null value. There can be several UNIQUE KEYS in a table.

Example:

Let’s consider a Student table having the following columns.

In the above columns, CityID is the UNIQUE KEY. Suppose, if a student leaves the city and goes abroad for studies, then that student’s CityID will not be there. In that case, that attribute will become null and null values are allowed in UNIQUE KEY.

7. FOREIGN KEY

A FOREIGN KEY in a table is an attribute that establishes a relationship between two tables. A FOREIGN KEY of one table references the PRIMARY KEY of another table, establishing the relation between the two tables. A FOREIGN KEY can accept multiple null and duplicate values.

Example:

Let’s consider the following two tables, the Students table, and the Order tables.

The first table is the Students table.

StuIDFNameLNameCity
1HarryKaneKolkata
2RonWiesleyNoida
3DobbyWrightMumbai

The second table is the Order table.

OrderIDOrderNoStuID
1654985453
2465468542
3216546983
4651654151

The StuID in the Students table is the PRIMARY KEY, and the StuID in the Order table is the FOREIGN KEY.

These are the essential keys in SQL that should be given importance while creating or dealing with databases.