A key is a single or combination of multiple fields in a table. It is used to fetch or retrieve records/data-rows from data table according to the condition/requirement. Keys are also used to create relationship among different database tables or views. SQL keys describe below.

Super Key: - Supper key is a set of one or more than one keys that can be used to identify a record uniquely in a table.

Example: - Primary key, Unique key, Alternate key is subset of Super Keys.

Primary Key: - The attribute or combination of attributes that uniquely identifies a row or record.

Syntax:-
-- Syntax of Primary Key
CREATE TABLE <TABLE_NMAE>
(
<COLUMN_NAME> <DATATYPE> PRIMARY KEY
)

Foreign Key: - Foreign Key is an attribute in database table that is Primary key in another table. It can accept multiple null, duplicate values.

Syntax:-
--Syntax of Foreign Key
CREATE TABLE <TABLE_NAME>
(
<COLUMN_NAME> <DATATAYPE> FOREIGN KEY REFERENCES <TABLE_NAME>(<COLUMN_NAME>)
)


Unique Key: - Unique key is a set of one or more attribute of a table that uniquely identify a record in database table. It is like Primary key but it can accept only one null value and it cannot have duplicate values.

Syntax:-
-- Syntax of Unique
CREATE TABLE <TABLE_NAME>
(
<COLUMN_NAME> <DATATYPE> UNIQUE
)

Composite Key: - Consists of two or more attributes is known as composite key. It can be a Candidate key or Primary key.

Candidate Key: - A column in a table which has the ability to become a primary key is called candidate key.

Alternate Key: -
Any of the candidate keys that are not part of the primary key is called an alternate key.

Secondary Key: - Alternate of primary key.

Example of Primary key, Foreign Key and Unique:-
-- CREATED FIRST TABLE
CREATE TABLE Department
(
ID INT PRIMARY KEY,
Name VARCHAR (50) NOT NULL,
Address VARCHAR (200) NOT NULL
)
 
--CREATED SECOND TABLE
CREATE TABLE Student
(
ID INT IDENTITY(1,1) PRIMARY KEY, -- PRIMARY KEY WITH IDENTITY PROPERTIES
RollNo VARCHAR(10) NOT NULL,
Name VARCHAR(50) NOT NULL,
EnrollNo VARCHAR(50) UNIQUE, -- UNIQUE KEY
Address VARCHAR(200) NOT NULL,
DeptID INT FOREIGN KEY REFERENCES Department(ID) -- FOREIGN KEY
)

  Modified On Sep-18-2014 01:24:05 PM

Leave Comment