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;