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
)
Anonymous User
03-May-2019Thank You.