In the relational model of databases, a Candidate Key of a relation is a minimal superkey for that relation; that is, a set of attributes such that
- the relation does not have two distinct tuples with the same values for these attributes
- there is no proper subset of these attributes for which holds.
Since a relation contains no duplicate tuples, the set of all its attributes is a superkey. It follows that every relation will have at least one candidate key. The candidate keys of a relation tell us all the possible ways we can identify its tuples. As such they are an important concept for the design database schema. For practical reasons RDBMSs usually require that for each relation one of its candidate keys is declared as the primary key, which means that it is considered as the preferred way to identify individual tuples. Foreign keys, for example, are usually required to reference such a primary key and not any of the other candidate keys.
In relational database design, a unique key or primary key is a candidate key to uniquely identify each row in a table. A unique key or primary key comprises a single column or set of columns. No two distinct rows in a table can have the same value (or combination of values) in those columns. Depending on its design, a table may have arbitrarily many unique keys but at most one primary key.
A Unique Key (UK) must uniquely identify all possible rows that exist in a table and not only the currently existing rows. Examples of unique keys are Social Security numbers, Passport Numbers, Permanent Account Number (associated with a specific person) or ISBNs (associated with a specific book). Telephone books and dictionaries cannot use names, words, or Dewey Decimal system (inLibrary System) numbers as candidate keys because they do not uniquely identify telephone numbers or words.
A Primary Key (PK) is a special case of unique keys. The major difference is that for unique keys the implicit NOT NULL constraint is not automatically enforced, while for primary keys it is enforced. Thus, the values in unique key columns may or may not be NULL. Another difference is that primary keys must be defined using another syntax.
The relational model, as expressed through relational calculus and relational algebra, does not distinguish between primary keys and other kinds of keys. Primary keys were added to the SQL standard mainly as a convenience to the application programmer.
An Alternate Key (or Secondary Key) is any candidate key which is not selected to be the primary key (PK), but it can be act like primary key. The alternate key also have all the defined properties of Primary key. this also uniquely identifies the record and this key also doesnot hold any null value. For Example consider an Employee Information table, suppose there are some columns like EmployeeId (PK), PAN Number, Passport Number etc. All these fields uniquely Identifies the employee, but EmployeeId is supposed as PK, and PAN/Passport numbers can be considered as Secondary/Alternate Key.
A Superkey can be defined as a set of attributes of a relation variable upon which all attributes of the reation variable are functionally dependent.
A Foreign Key (FK) is a referential constraint between two tables.
The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table. The columns in the referencing table must be the primary key or other candidate key in the referenced table. The values in one row of the referencing columns must occur in a single row in the referenced table. Thus, a row in the referencing table cannot contain values that don’t exist in the referenced table (except potentially NULL). This way references can be made to link information together and it is an essential part of database normalization. Multiple rows in the referencing table may refer to the same row in the referenced table. Most of the time, it reflects the one (master table, or referenced table) to many (child table, or referencing table) relationship.
The referencing and referenced table may be the same table, i.e. the foreign key refers back to the same table. Such a foreign key is known as self-referencing or recursive foreign key.
A table may have multiple foreign keys, and each foreign key can have a different referenced table. Each foreign key is enforced independently by the database system. Therefore, cascading relationships between tables can be established using foreign keys.
Improper foreign key/primary key relationships or not enforcing those relationships are often the source of many database and data modeling problems.
ø A relational database matches data using common characteristics found within the data set. The resulting groups of data are organized and are much easier for people to understand.
ø ø Referential integrity is a property of data which, when satisfied, requires every value of one attribute (column) of a relation (table) to exist as a value of another attribute in a different (or the same) relation (table).
For referential integrity to hold, any field in a table that is declared a foreign key can contain only values from a parent table’s primary key or a candidate key. For instance, deleting a record that contains a value referred to by a foreign key in another table would break referential integrity. Some relational database management systems (RDBMS) can enforce referential integrity, normally either by deleting the foreign key rows as well to maintain integrity, or by returning an error and not performing the delete. Which method is used may be determined by a referential integrity constraint defined in a data dictionary.
øøø A functional dependency (FD) is a constraint between two sets of attributes in a relation from a database.
øøøø A compound key is a key that consists of 2 or more attributes that uniquely identify an entity occurrence. Each attribute that makes up the compound key is a simple key in its own right.
This is often confused with a composite key whereby even though this is also a key that consists of 2 or more attributes that uniquely identify an entity occurrence, at least one attribute that makes up the composite key is not a simple key in its own right.
