TRAINING (SQL-3) - Different Keys in SQL

1.) PRIMARY KEY: 

• A primary key is a column or set of columns which uniquely identify a row and is not null (Unique + Not Null). 
• A table can have only one Primary Key.
• Primary can be made of a single or multiple column.
• Primary key are almost 'Always' implemented in most of RDBMS using an **index.

** An index is a special type of database object that permit fast search of column values as compare to searching the table. As the new rows are inserted into the table, RDBMS automatically searches the index to make sure the value for primary key of the new row is not already in use in table and rejects the insert request if it have

2.) FOREIGN/REFERENTIAL KEY:

• A foreign key is the column or set of columns that is stored in the child table to associate/connect it with parent table.
• A table can have zero, one or many foreign keys.
• Referential/Foreign key/constraint enforces RDBMS to automatically check and ensure that each foreign key value in the child table must always have a primary key value present in child table.
• With referential integrity constraint in place on a table:
• An insert of a new row in a child table will be failed/rejected, if the corresponding parent table row does not exists.
• An update to a foreign key value in child table will failed/rejected, if the new value of the foreign key does not exists in the corresponding parent table.
• A delete on row from parent table will failed/rejected, if a parent row has related rows (foreign key) in one or more child tables. (If you want this delete to successful, either child table rows must be deleted along with parent table rows using CASCADING DELETE option)
• In most of RDBMS the foreign key must either be primary key of parent table or columns/set of columns for which a unique index is defined.
• Sometimes Parent and child tables can be same (RECURSIVE RELATIONSHIP).
• In some cases foreign key may be part of primary key.

A) CANDIDATE KEY:

• An entity/table may have one or more choices for the primary key. Collectively these are known as CANDIDATE KEYS.
• For example, an employee has an employee number, a National Insurance (NI) number and an email address. 

B) SECONDARY KEY:

• An entity/table may have one or more choices for the primary key. Collectively these are known as CANDIDATE KEYS. One is selected as the primary key. Those not selected are known as SECONDARY KEYS.
• For example, an employee has an employee number, a National Insurance (NI) number. If the employee number is chosen as the primary key then the NI number is secondary key. 

C) SIMPLE KEY

• A simple key consists of a single column to uniquely identify a row occurrence.
• For example, a student number, which uniquely identifies a particular student. 

D) COMPOSITE/CONCATENATED/COMPOUND KEY

• When two or more columns uniquely identify a row, this is referred as Composite or Concatenated or Compound Key.
• Each attribute, which makes up the key, is also a simple key in its own right.
• For example, we have an entity named enrollment ( holds the courses on which a student is enrolled, a student is allowed to enroll on more than one course. This has a compound key of both student number and course number, which is required to uniquely identify a student on a particular course.) In this scenario: 
• Student number and course number combined is a compound primary key for the enrollment entity.
• Student number in the enrollment entity is a simple key in its own right, which is used as a foreign key to link to the student entity.
• Course number in the enrollment entity is a simple key in its own right, which is used as a foreign key to link to the course entity.

E) SYNTHETIC KEY

• If a composite primary key has many columns, for simplicity we can create a synthetic key. 
• Synthetic key does not have any meaning to the users and used for simplicity only.

Master & Details table concept:

In whichever table PRIMARY KEY is present - MASTER TABLE - Eg. DEPT Table

In whichever table FOREIGN KEY is present - DETAILS TABLE - Eg. EMP Table