Different Types of SQL Keys in Database with Example

multiple primary keys sql, foreign keys sql , keys in sql, sql foreign keys

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.

primary, primary key, constraint definition, candidate key, define constraint
Types of keys

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.

Read Also:  Laravel Eloquent query where exists MySQL

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.

Read Also:  Laravel 6 Get All query string parameters

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.

Read Also:  The PHP Framework Laravel installation

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.

About Pakainfo

I am Jaydeep Gondaliya, a software engineer, the founder and the person running Pakainfo. I'm a full-stack developer, entrepreneur and owner of Pakainfo.com. I live in India and I love to write tutorials and tips that can help to other artisan, a Passionate Blogger, who love to share the informative content on PHP, Javascript, JQuery, Laravel, Codeigniter, VueJS, AngularJS and Bootstrap from the early stage.

View all posts by Pakainfo →

Leave a Reply

Your email address will not be published. Required fields are marked *