keys in DBMS foreign key, Primary key, Candidate key, Alternate key
Keys in a relational model in DBMS -it is essential 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 cores in the information that Below describes –foreign key, Primary key, Candidate key, Alternate key.
The 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 link, a collection of one or more attributes having values that sure are unique within a connection. Can 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. Suppo 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 a question and the combination of SuppoNo and detail. 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 attribute declares a primary key; in such cases, the first essential consists of more than one quality; 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 refer 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 crucial central position. Occasionally we may encounter a relationship 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 the 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 the supplier’s 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 derived from the primary key of some other table, is known as foreign-key in the current schedule. A foreign key is used to represent the relationship between two tables. A foreign key in non-key attribute whose value derived from the primary key of another table.or in other words. .a non-key quality 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 different schedule. 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-