SQL concept about keys

SQL Server

What is RDBMS and its concept?

RDBMS called for Relational Database Management System. RDBMS is the basis for Structure Query language, and for all latest database systems like MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.

Keys:-

Candidate key:-

It is important to have an attribute in a table that uniquely identify a row;

An attribute or set of attribute that uniquely identify a row is called a candidate key

#Vehicle

Serial#	Region#	Description#
023452	5602	Leyland
021545	985	Volvo
025555	9988	Toyto

All three are unique, all three are candidate key

Primary Key:-

You choose to identify each row uniquely is called the primary key

Serial#	Reigon#	Description
01	03	Lelayand
02	05	volvo
03	03	toyto

Serial# is a primary key

Alternate key/Unique key

May have the value Null, A null values is not to be permitted in a primary key.

#serialNo	#ReistrationNumber
101	155
102	NULL
103	5555

#ReistrationNumber is uniquely identify is called alternate key

Composite key:-

A single attribute cannot be used to identify rows uniquely and a combition of two or more attribute is used as a primary key, such keys are called composite key

Purchase table

CustomerCode	Productcode	Qty_purchase	Purchase date
C122	P002	12	2/15/13
C135	P005	15	2/19/13
C2555	P002	10	2/23/13
C144	P005	17	2/25/13

A combination of customer code and product code result in all unique values

Foreign key

When a primary key of one table appears as an attribute in another table, it is called the foreign key in the second table

A foreign key is used to relate two tabled.

Department      

DepartmentCode	DepartmentName	Location
PHY	Physics	P-block
MAT	Math	M-block
CHE	Chemistry	C-block

Faculty Member

FacultyMemberCode	Facultyname	DepartmentCode
0025	Jim	CHE
0026	Gourav	MAT
0027	Ritesh	CHE

How to create a table?

Create table Customer

(

Id int,

Name varchar(45)

)

How to insert the Primary key?

Create table Customer

(

Id int constraint pkid primary key,

Name varchar(45)

)

How to insert the Foreign key?

Create table saler

(

Id int constraint fkid Foregin  key reference Customer(id),

Name varchar(45)

)

How to insert the data in to table?

Insert into customer values(1,”JIM”)