Keys in DBMS (database management system)
In the relational model, keys are important because they are used to ensure that each row in a table is uniquely identifiable. They are also used to establish relationships among tables and to ensure the integrity of the data . A key consists of one or more attributes that determine other attributes. For example, an invoice number identifies all of the invoice attributes, such as the invoice date and the customer name.
Why we need DBMS keys?
We use a key for defining various types of integrity constraints in a database. A table, on the other hand, represents a collection of the records of various events for any relation. Now, there might be thousands of these records, and some of these might even be duplicated.
Thus, we need a way in which one can identify all of these records uniquely and separately, i.e., without any duplicates. This hassle is removed with the help of keys.
For example, let us consider a database of all the students who are studying in a college. What attribute of all the students, according to you, will identify each of these people uniquely? We can refer to these students by their names, departments, sections, and year. Similarly, we can also mention only the university roll number and fetch all the other details based on that roll number.
The keys in DBMS can be a combination of multiple attributes (or columns), or they can be just one single attribute. The primary motive of the keys is to provide every record with a unique identity of its own.
Types of Keys in DBMS
Super Key A super key refers to the set of all those keys that help us uniquely identify all the rows present in a table. It means that all of these columns present in a table that can identify the columns of that table uniquely act as the super keys.
A super key is a candidate key’s superset (candidate key has been explained below). We need to pick the primary key of any table from the super key’s set so as to make it the table’s identity attribute..click here to picture explain
Primary Key primarykey in DBMS is a column or group of columns in a table that uniquely identify every row in that table. The Primary Key can’t be a duplicate meaning the same value can’t appear more than once in the table.
A table cannot have more than one primary key. click here to picture explain
Rules for defining Primary key:
Two rows can’t have the same primary key value ,It must for every row to have a primary key value , The primary key field cannot be null.,The value in a primary key column can never be modified or updated if any foreign key refers to that primary key.
Candidate Key
Candidate in SQL is a set of attributes that uniquely identify tuples in a table. Candidate Key is a super key with no repeated attributes. The Primary key should be selected from the candidate keys. Every table must have at least a single candidate key. A table can have multiple candidate keys but only a single primary key. click here to more picture explain
Foreign Key
Foreignkey is a column that creates a relationship between two tables. The purpose of Foreign keys is to maintain data integrity and allow navigation between two different instances of an entity. It acts as a cross-reference between two tables as it references the primary key of another table. click here to more picture explain
Alternate Key
Alternate Keys is a column or group of columns in a table that uniquely identify every row in that table. A table can have multiple choices for a primary key but only one can be set as the primary key. All the keys which are not primary key are called an Alternate Key.click here to get more picture explain
Composite Key
Composite Key is a combination of two or more columns that uniquely identify rows in a table. The combination of columns guarantees uniqueness, though individually uniqueness is not guaranteed.
Hence, they are combined to uniquely identify records in a table.click here to picture explain
Compound key
A Compound Key is a primary key that does not consist of a single column but two or more columns that allow us to distinctly identify a particular row. For a compound key, we do not have any column that is unique in itself; therefore we need to combine two or more columns to make them unique.
Let us look at an example of a table consisting of product and product details. In this table, we can see that a product can be ordered by more than one customer, and more than one product can be present in order. Therefore we need to combine both the OrderId and the ProductId to create a unique way of identifying the row.
