What are the different types of keys in RDBMS

Question

What are the different types of keys in RDBMS   Please include examples with your answer

User · Answer

There is also a SURROGATE KEY  it occurs if one non prime attribute depends on another non prime attribute  that time you don t now to choose which key as primary key to split up your table  In that case use a surrogate key instead of a primary key  Usually this key is system defined and always have numeric values and its value often automatically incremented for new rows  Eg   ms acces   auto number  amp  my SQL   identity column  amp  oracle   sequence

User · Answer

lafur forgot the surrogate key      A surrogate key in a database is a unique identifier for either an entity in the modeled world or an object in the database  The surrogate key is not derived from application data

User · Answer

There also exists a UNIQUE KEY  The main difference between PRIMARY KEY and UNIQUE KEY is that the PRIMARY KEY never takes NULL value while a UNIQUE KEY may take NULL value  Also  there can be only one PRIMARY KEY in a table while UNIQUE KEY may be more than one

User · Answer

I  Super Key     An attribute or a combination of attribute that is used to identify the records uniquely is known as Super Key  A table can have many Super Keys   E g  of Super Key   ID ID  Name ID  Address ID  Department ID ID  Salary Name  Address Name  Address  Department ID   So on as any combination which can identify the records uniquely will be a Super Key    II  Candidate Key     It can be defined as minimal Super Key or irreducible Super Key  In other words an attribute or a combination of attribute that identifies the record uniquely but none of its proper subsets can identify the records uniquely   E g  of Candidate Key   ID Name  Address   For above table we have only two Candidate Keys  i e  Irreducible Super Key  used to identify the records from the table uniquely  ID Key can identify the record uniquely and similarly combination of Name and Address can identify the record uniquely  but neither Name nor Address can be used to identify the records uniquely as it might be possible that we have two employees with similar name or two employees from the same house    III  Primary Key     A Candidate Key that is used by the database designer for unique identification of each row in a table is known as Primary Key  A Primary Key can consist of one or more attributes of a table   E g  of Primary Key - Database designer can use one of the Candidate Key as a Primary Key  In this case we have    ID    and    Name  Address    as Candidate Key  we will consider    ID    Key as a Primary Key as the other key is the combination of more than one attribute    IV  Foreign Key     A foreign key is an attribute or combination of attribute in one base table that points to the candidate key  generally it is the primary key  of another table  The purpose of the foreign key is to ensure referential integrity of the data i e  only values that are supposed to appear in the database are permitted   E g  of Foreign Key     Let consider we have another table i e  Department Table with Attributes    Department ID        Department Name        Manager ID        Location ID    with Department ID as an Primary Key  Now the Department ID attribute of Employee Table  dependent or child table  can be defined as the Foreign Key as it can reference to the Department ID attribute of the Departments table  the referenced or parent table   a Foreign Key value must match an existing value in the parent table or be NULL    V  Composite Key     If we use multiple attributes to create a Primary Key then that Primary Key is called Composite Key  also called a Compound Key or Concatenated Key    E g  of Composite Key  if we have used    Name  Address    as a Primary Key then it will be our Composite Key    VI  Alternate Key     Alternate Key can be any of the Candidate Keys except for the Primary Key   E g  of Alternate Key is    Name  Address    as it is the only other Candidate Key which is not a Primary Key    VII  Secondary Key     The attributes that are not even the Super Key but can be still used for identification of records  not unique  are known as Secondary Key   E g  of Secondary Key can be Name  Address  Salary  Department ID etc  as they can identify the records but they might not be unique

User · Answer

Sharing my notes which I usually maintain while reading from Internet  I hope it may be helpful to someone   Candidate Key or  available keys  Candidate keys are those keys which is candidate for primary key of a table  In simple words we can understand that such type of keys which full fill all the requirements of primary key which is not null and have unique records is a candidate for primary key  So thus type of key is known as candidate key  Every table must have at least one candidate key but at the same time can have several   Primary Key  Such type of candidate key which is chosen as a primary key for table is known as primary key  Primary keys are used to identify tables  There is only one primary key per table  In SQL Server when we create primary key to any table then a clustered index is automatically created to that column   Foreign Key  Foreign key are those keys which is used to define relationship between two tables  When we want to implement relationship between two tables then we use concept of foreign key  It is also known as referential integrity  We can create more than one foreign key per table  Foreign key is generally a primary key from one table that appears as a field in another where the first table has a relationship to the second  In other words  if we had a table A with a primary key X that linked to a table B where X was a field in B  then X would be a foreign key in B   Alternate Key or Secondary  If any table have more than one candidate key  then after choosing primary key from those candidate key  rest of candidate keys are known as an alternate key of  that table  Like here we can take a very simple example to understand the concept of alternate key  Suppose we have a table named Employee which has two columns EmpID and EmpMail  both have not null attributes and unique value  So both columns are treated as candidate key  Now we make EmpID as a primary key to that table then EmpMail is known as alternate key   Composite Key  When we create keys on more than one column then that key is known as composite key  Like here we can take an example to understand this feature  I have a table Student which has two columns Sid and SrefNo and we make primary key on these two column  Then this key is known as composite key   Natural keys  A natural key is one or more existing data attributes that are unique to the business concept   For the Customer table there was two candidate keys  in this case CustomerNumber and SocialSecurityNumber  Link http   www agiledata org essays keys html  Surrogate key  Introduce a new column  called a surrogate key  which is a key that has no business meaning  An example of which is the AddressID column of the Address table in Figure 1  Addresses don t have an  easy  natural key because you would need to use all of the columns of the Address table to form a key for itself  you might be able to get away with just the combination of Street and ZipCode depending on your problem domain   therefore introducing a surrogate key is a much better option in this case  Link http   www agiledata org essays keys html  Unique key  A unique key is a superkey--that is  in the relational model of database organization  a set of attributes of a relation variable for which it holds that in all relations assigned to that variable  there are no two distinct tuples  rows  that have the same values for the attributes in this set  Aggregate or Compound keys  When more than one column is combined to form a unique key  their combined value is used to access each row and maintain uniqueness  These keys are referred to as aggregate or compound keys  Values are not combined  they are compared using their data types   Simple key  Simple key made from only one attribute    Super key  A superkey is defined in the relational model as a set of attributes of a relation variable  relvar  for which it holds that in all relations assigned to that variable there are no two distinct tuples  rows  that have the same values for the attributes in this set  Equivalently a super key can also be defined as a set of attributes of a relvar upon which all attributes of the relvar are functionally dependent   Partial Key or Discriminator key  It is a set of attributes that can uniquely identify weak entities and that are related to same owner entity  It is sometime called as Discriminator

User · Answer

From here and here   after i googled your title         Alternate key - An alternate key is any candidate key which is not selected to be the primary key   Candidate key - A candidate key is a field or combination of fields that can act as a primary key field for that table to uniquely identify each record in that table    Compound key - compound key  also called a composite key or concatenated key  is a key that consists of 2 or more attributes    Primary key - a primary key is a value that can be used to identify a unique row in a table  Attributes are associated with it  Examples of primary keys are Social Security numbers  associated to a specific person  or ISBNs  associated to a specific book     In the relational model of data  a primary key is a candidate key chosen as the main method of uniquely identifying a tuple in a relation    Superkey - A superkey is defined in the relational model as a set of attributes of a relation variable  relvar  for which it holds that in all relations assigned to that variable there are no two distinct tuples  rows  that have the same values for the attributes in this set  Equivalently a superkey can also be defined as a set of attributes of a relvar upon which all attributes of the relvar are functionally dependent    Foreign key - a foreign key  FK  is a field or group of fields in a database record that points to a key field or group of fields forming a key of another database record in some  usually different  table  Usually a foreign key in one table refers to the primary key  PK  of another table  This way references can be made to link information together and it is an essential part of database normalization

User · Answer

Partial Key   It is a set of attributes that can uniquely identify weak entities and that are related to same owner entity  It is sometime called as Discriminator   Alternate Key   All Candidate Keys excluding the Primary Key are known as Alternate Keys   Artificial Key   If no obvious key  either stand alone or compound is available  then the last resort is to simply create a key  by assigning a unique number to each record or occurrence  Then this is known as developing an artificial key   Compound Key   If no single data element uniquely identifies occurrences within a construct  then combining multiple elements to create a unique identifier for the construct is known as creating a compound key   Natural Key   When one of the data elements stored within a construct is utilized as the primary key  then it is called the natural key

[rdbms] What are the different types of keys in RDBMS?

Examples related to rdbms