keys in DBMS foreign key, Primary key, Candidate key, Alternate key
keys in relational model in DBMS -it is important to be able to specify how rows in a relation are distinguished conceptually, rows are distinct from one another, but from a database perspective, the difference among them must be expressed in terms of their attributes. keys come here a rescue. We know that information uses tables to arrange info. to keep up knowledge integrity (that is knowledge ought to be correct and in we tend toll formed) we use thought of keys. There are unit 4 varieties of keys in information that Below describes –foreign key, Primary key, Candidate key, Alternate key.
Primary key in SQL (DBMS Primary key)
A primary key is a set of one or more attributes that can uniquely identify tuples within the relation. Within a given relation, a set of one or more attributes having values that sure are unique within relation and thus are able to uniquely identify that tuple is said to be the primary key in SQL of the relation.
Every relation does have a primary key in SQL.in our sample database, SuppNo is the primary key in SQL suppliers as contains a unique value for each tuple in the relation. Similarly, an item is the primary key for an item and the combination of SuppoNo and item No is a primary key for shipments relation.
In some tables, a combination of more than one attribute provides a unique value for each row. In such tables, a group of these attributes is declared as a primary key, in such cases, the primary key consists of more than one attribute, it is called composite-primary-key.
A primary key in SQL is nonredundant it does not have duplicate value in the same relation. The non-primary-key attributes of a table can be referred to as non-key attributes.
Candidate key in DBMS (Candidate key in SQL)
All attribute combinations inside a relation that can serve as primary key are Candidate keys as they are Candidates for the primary key position. Occasionally we may encounter a relation in which there is more than one attribute possessing the unique identification property.
in our sample database, there are two candidate keys suppoNano and Supp-Name in Supplier’s relation. Both of these attributes contain unique values for each tuple.similarly, in items, item no and item-Name are candidate keys.
In the case of two or more candidate keys, only database analyst decides one of them as the primary for the relation.
A Candidate key that is not the primary key is called an Alternate key. In case of two or more candidate keys, only one of them serves as the primary key in SQL. The rest of them as they are alternates only.
In suppliers table, Supp_name is the alternate key and in items table, item_Name is the alternate key.
Foreign key (foreign key in SQL, foreign key in DBMS)
A non-key attribute, whose value is derived from the primary key of some other table, is known as foreign-key in the current table. A foreign key is used to represent the relationship between two tables. a foreign key is a non-key attribute whose value is derived from the primary key of another table.or in other words, a non-key attribute of a table, which is the primary key of some other table is known as a foreign key in SQL.
The table in which this non-key attribute the foreign key attribute exists is called a foreign table or Detail table, and table that defines a primary key, which the foreign key in SQL of detail-table refers to, is called Primary table or Master table.
Read also Related article-