Home > DeveloperSection > Blogs > SQL Keys

SQL Keys


Database Database 
Ratings:
0 Comment(s)
 1711  View(s)
Rate this:

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

)


Don't want to miss updates? Please click the below button!

Follow MindStick