KeyDBMS

Introduction

Keys(Name clearly define)

A key part of a relational database and a vital part of the structure of a table. They ensure each record within a table can be uniquely identified by one or a combination of fields within the table. They help enforce integrity and help identify the relationship between tables.

Keys

Super Key

Candidate Key

Primary Key

Super Key

A Super key is any combination of fields within a table that uniquely identifies each record within that table.

BookBook ID Name Author

B1 Xyz A1

B2 ABC A1

B3 XYZ A2

B4 PQR A3

B5 RST A1

B6 ABC A3

Candidate Key

Candidate key is a subset of super key

A candidate key is a single field or the least combination of fields that uniquely identifies each record in table

The least combination of fields distinguishes a candidate key from a super key.

Every table must have at least one candidate

Properties Unique

Not null

Contains minimum number of fields to ensure uniqueness

Must uniquely identify each record in the table

Book ID Name Author

B1 XYZ A!

B2 ABC A1

B3 XYZ A2

B4 PQR A3

B5 RST A1

B6 ABC A3

Name BookId Author Name Author

Primary Key

It is a candidate key

Uniquely identify a specific instance of an entity

Primary key cannot contain any Null value because we cannot uniquely identify multiple Null values.

Properties Stable

Minimal

Definitive

Accessible

Book ID Name Author

B1 Xyz A!

B2 ABC A1

B3 XYZ A2

B4 PQR A3

B5 RST A1

B6 ABC A3

Book ID Name Author

CREATE TABLE Persons(

P_Id int NOT NULL PRIMARY KEY,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255)

);

CREATE TABLE Person

(

P_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

);

Foreign Key

Foreign key is a field in a relational table that matches the primary key column of another table.

It can be used to link two tables together

CREATE TABLE supplier

( supplier_id numeric(10) not null,

supplier_name varchar2(50) not null,

contact_name varchar2(50),

CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)

);

CREATE TABLE products

( product_id numeric(10) not null,

supplier_id numeric(10) not null,

CONSTRAINT fk_supplier

FOREIGN KEY (supplier_id)

REFERENCES supplier(supplier_id)

);

Remaining

Alternate Key

Remaining candidate key which are not selected as primary key

Composite Key

Primary key made up of more than one attribute