Hello friends welcome back to SQL with Manoj today in this video I'm going to talk about types of keys in rdbms so normally you would have heard about primary Keys unique keys and foreign keys so these normally these keys are associated with a SQL product like SQL server or Oracle or IBM db2 whatever it is but today I'm going to talk about some different kind of keys and those are related to rdbms that is relational database management system okay so when you talk about keys in rdbms these are the following keys that you discuss right one is the candidate key second is the primary key third is the alternate key then this is the super key and we have a composite key okay so now what are these keys and what is their significance and how do they help us in designing our database tables okay so before going to this keys let me let me go to this table employe table okay and let's I have to design an employee table not only I have to design employee table but I have to make sure you know that it follows some Integrity rules like uh it should have a primary key and you know primary key that I'm going to design should be a proper it should not be just a random column okay so how can I decide which column is good candidate for a primary key okay so in employee table you have typical these kind of columns there can be multiple but right now I have employee ID employee name date of birth date of joining so social security number Department ID and manager ID so now how can I decide which column will be a primary key right so if you have a good experience with SQL server and database designing so you can just go ahead and you know say that okay employe ID could be my primary key but there could be some complex scenarios the table could not be an employee table the the table could be a some some some complex order history or some you know purchase so so so take this the scenario can be complex and you will have to decide by you know going through the database rdbms principles right which column you will have to decide as a primary key right so these are the principles that will let you decide what will be your key column that will assign it as a primary key right so first of all in your table you have to you have to identify what are your candidate keys right so first of all what is a candidate key a candidate key is the attribute or a column or these are a set of attribute or columns in a relation or table that qualifies for uniqueness of each tle or row okay so if you're confused with you know attribute columns and all these uh words so let me tell you first of all that attribute is nothing but attribute is a column attribute you call in dbms and column in SQL terms okay relation is nothing but a table okay and similarly a tle is nothing but a row okay so it says that a candidate key is any column or a set of columns combined in a table that qualifies for uniqueness of that table okay a relation table a relation or a table can have one or more than one candidate Keys a candidate key is also known as a minimal Super Key okay super key we'll see later on so first of all let's see how many candidate Keys we can have in this table right so an employee ID will be always unique in this particular employee table so employee ID can be a candidate key right employee name we can have multiple people with same name so this column cannot be unique date of birth also can occur many of times because two employees can you know born same day date of joining lot of people join on same day so so we cannot have you know unique number of people joining on a same day right social security number is another column that is unique for in each and every employee not only the department but all over the world right so the social security number is my another candidate key Department ID more than one person can work in a department so no manager ID similar to the department and date of birth and date of joining more than one employee can report to a same manager so this cannot be a candidate key so my candidate key columns here can be my em P L O ye e employee ID and another column can be SSN okay so now I'm done with my first rule right so the candidate key can be these two columns right now how can I decide my primary key which one will be my primary key right so the primary key is the candidate key attribute or column that is most suited to maintain uniqueness in a table at the tle or row level right so it is upon your discretion that you know which column do you want to select as a primary key right so now you have two options employee ID or SSL so it's up to you you have to decide which column will be your primary key SSN definitely it cannot be a primary key because it's a secret number it should not be displayed and it cannot be uh shown publicly so this is a bad bad candidate key definitely then you know we have only one choice we could have come up with some more candidate Keys like u i could have a employee name plus date of birth right but there are you know slight chances of clashes so employee name Plus date of birth right so there but there is still a very Corner scenario that you know same two employees with same name can be born on same day right it's a narrow scenario so let's do not take this up okay so this is not an ideal scenario I'll just uh cross it okay so employee ID is the only one key column that can be used as a primary key okay now the Third is the alternate key what is an alternate key these are the other candidate key attribute of columns that you didn't choose as a primary key column okay so of all these candidate keys I just chose this primary key so this SSN number and the employee ID and the date of birth other candidate key will become the alternate keys right so so this is not the picture now because there is um there are you know chances of clashes here so my SSN will be the alternate key here here okay now coming over to Super Key what is a Super Key a Super Key is a super set of a candidate key right if you add any other attribute or column to the candidate key then it becomes a Super Key right like word so if I have this employee ID column right if I have this employee ID column if I let's say if I add employee name then it becomes a Super Key okay I can even add add date of birth this will still maintain the uniqueness of the table right because employee ID is always unique if I'm going to add employee name and date of birth the table will still be uniquely identified by all these three columns so a Super Key can be a set of these two columns a Super Key can be a set of these three columns right so a Super Key is nothing but if you add any other non-key attribute to the key attribute that key becomes a Super Key okay then what what is a composite key right so there might be a case you know when you don't have employee ID column in a table they'll not be a scenario but let's say there is a scenario where you didn't where you don't have a employee ID so what it so what it says that if a table do not have a single column that qualifies for a candidate key then you have to select two or more columns to make a row unique right so if you don't have employee ID as a column in the table then you have to identify you know other columns to make that uh table unique so what you can do is you can check like what are the ways in in which your table data can maintain uniqueness right you have to identify those set of attribute or columns right so in our U candidate key section we selected employee name plus date of birth but but we saw that you know two employees uh were born on same day right so we cannot have that so what we can do we can either join we can either have a a date of joining on that day but still there is a very very corner and a very very rare scenario where you know same set of employee born on same day also joined on same day there can be a scenario yeah there can be a scenario so if the scenario can be there you can still have an option to add the manager ID right so ideally the com ideally your primary key or the composite key should not be this long right but you have have to judge yourself and you have to make a decision that you know how long how long and how composite can be your primary key uh that can make your table unique right so if you don't have an option you have to go this long but it's ideally suited that you know your one key column should be uh sufficient for maintaining the uh table uniqueness but if it is not then you can add second column if it is not then you have to add a third column and so on so forth so uh so so this is the way you know you design your table and you design your uh primary key and so that you know you can maintain your uniqueness okay so this is all about uh types of keys in dbms hope you like this uh video and like this session and please let me know if you have any suggestion thank you very much for watching this video please subscribe by subscribing you will get to know about my coming videos okay thanks a lot have a nice day