DBMS Keys

Keys

Keys are very important part of Relational database.
They are used to establish and identify relation between tables.

They also ensure that each record within a table can be uniquely identified by combination of one or more fields within a table.

Different keys

• Super Key :Super Key is defined as a set of attributes within a table that uniquely identifies each record within a table. Super Key is a superset of Candidate key.
• Candidate keys: Candidate Keys  are defined as the set of fields from which primary key can be selected. It is an attribute or set of attribute that can act as a primary key for a table to uniquely identify each record in that table.
• Primary key: Primary Key is a candidate key that is most appropriate to become main key of the table. It is a key that uniquely identify each record in a table.
• Composite key : Key that consist of two or more attributes that uniquely identify an entity occurance is called Composite key. But any attribute that makes up the Composite key is not a simple key in its own.
• Secondary keys or alternative keys : The candidate key which are not selected for primary key are known as secondary keys or alternative keys.
• Foreign key: An attribute (or combination of attributes) in one table whose values must either match the primary key in another table or be null.

Primary Key

• A primary key is a field in a table which uniquely identifies each row in a table.
• Primary keys must contain unique values.
• A primary key column cannot have NULL values.
• A table can have only one primary key, which may consist of single or multiple fields.

How to create a primary key?

Use attribute primary key to make key primary key.

For example:

CREATE TABLE Student( RollNumber INT, NAME VARCHAR (20), PRIMARY KEY (RollNumber) );

How to delete a primary key?

ALTER TABLE Student DROP PRIMARY KEY ;

Foreign Key

A foreign key is a key used to link two tables together. This is sometimes also called as a referencing key.
Foreign key matches a primary key in another table.

How to create a foreign key?

Use reference keyword to make a key foreign key.

For example:

CREATE TABLE College( CollegeCode INT NOT NULL,
Student_ID INT references Student(RollNumber),
PRIMARY KEY (CollegeCode) );

How to delete a foreign key?

ALTER TABLE Student DROP FOREIGN KEY;