SQL Keys play a necessary role in database related modules in SQL such as a get a large of data/record from the Database table according to the requirement also we learn multiple primary keys sql with define constraint and constraint definition. A key can be defined as a single or combination of multiple fields/columns in a Database table. With the help of keys i not only can get a large of data but also used to make a relationship among different database tables. Therefor, in this tutorial I will talk about about the theory as well as types of keys used in SQL SERVER.
Types of keys used in SQL SERVER
There are different types of keys I can use in SQL SERVER which are as follows:-
- Super Key
- Candidate Key
- Primary Key
- Alternate key
- Composite/Compound Key
- Unique Key
- Foreign Key
Now step by step i will talk about theses different types of keys used in SQL SERVER.
1. Super Key
Super key can be determine as a set of multiple than one keys that can be used to identify a record/data uniquely in a Database table. This key includes only those DB table fields which includes unique value as if i take an example of Student than Student_Id will be the field which includes unique value and it become easy to identify the employee from Student_Id field.
For Example : -Keys which can be the subset of Super Key are Primary key, Unique key and Alternate key. As right now i don’t know about these keys so further i will talk about these keys.
2. Primary Key
Primary key can be determine as a set of multiple DB table fields/columns of a table that uniquely identify a record in database table. Record can be uniquely identify when the column which includes unique value like Student_Id of employee from an organization. It will not accept null values and duplicate values. Only one primary key can be exist in a single Database table not more than one.
For Example:- Assume a Database table exist of Student data with DB table fields Student_Name, Student_Address,Student_Id and Student_Designation so in this table only one field is there which is used to uniquely identify detail of Student that is Student_Id.
3. Unique Key
Unique key can be determine as a set of multiple DB table fields/columns of a table that have the capability to uniquely identify a record in database table. We can have other DB table fields also in a Database table beyond primary key which are also able to uniquely identify the record. It can accept only one null value and it can not have duplicate values in it.
For Example:-Assume a Database table exist of Student data with DB table fields Student_Name, Student_Address,Student_Id , Student_Designation and Student_MobileNo so in this Database table except Student_Id i also have an another field named Student_MobileNo which is can also be used to uniquely identify the record as well as can termed as Unique Key.
4. Alternate key
Alternate key can be determine as a key that can be work as a primary key if required. We can also understand this key as a candidate for primary key as candidate key but right now it is not a primary key.
For Example:- Assume a Database table exist of Student data with DB table fields Student_Name, Student_Address,Student_Id , Student_Designation and Student_MobileNo in this case Student_MobileNo can be the alternate key as it is also suitable to identify the record uniquely but right now it is not primary key.
5. Candidate Key
Candidate Key can be determine as a set of multiple DB table fields/columns that can identify a record uniquely in a Database table like primary key or i can also say that other DB table fields than primary key which can become primary key and a table can have more than one candidate key. Each candidate key can work as primary key if required in any case.
For Example:- Assume a Database table exist of Student data with DB table fields Student_Name, Student_Address,Student_Id , Student_Designation.Student_UIDAINo and Student_MobileNo in this Database table Student_MobileNo and Student_UIDAINo are Candidate Keys as these two DB table fields can also work as candidate key.
6. Composite/Compound Key
Composite Key can be determine as a combination of more than one DB table fields/columns of a table to uniquely identify the record. Fields which can be combine to make composite key can be candidate, primary key.
For Example:-Assume a Database table exist of Student data with DB table fields Student_Name, Student_Address,Student_Id , Student_Designation, Student_UIDAINo and Student_MobileNo in this Database table to build a composite key i combine Student_Id and Student_MobileNo to fetch data from table.
7. Foreign Key (foreign keys sql)
Foreign Key can be determine as a field/column in the School Database table that is Primary key in Student Database table. It can also accept multiple null values and duplicate values. This can be simply understand with the help of example given below.
For Example:- We can have a Student_Id column in the School Database table which is pointing to Student_Id column in a Student Database table where it a primary key. So with the help of foreign key i can simply identify the data from tables.
Student Table
CREATE TABLE Student ( Student_Id int PRIMARY KEY, --primary key Student_Name varchar (50) NOT NULL, Student_Address varchar (200) NOT NULL, Student_Designation varchar (50) NOT NULL, Student_UIDAINo varchar (50) NOT NULL, Student_MobileNo varchar (50) NOT NULL )
School Table
CREATE TABLE School ( School_ID int PRIMARY KEY, --primary key School_Name varchar(50) NOT NULL, School_RegisterNo varchar(50) UNIQUE, --unique key School_Address varchar(200) NOT NULL, Student_ID int FOREIGN KEY REFERENCES Student(Student_Id) --foreign key )
This is the Full theory of different keys Information used in SQL SERVER.
I hope you get an idea about multiple primary keys sql.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.