what are the differences between primary, foreign, and unique keys

The one thing that primary, unique, and foreign keys all have in common is the fact that each type of key can consist of more than just one column from a given table. In other words, foreign, primary, and unique keys are not restricted to having just one column from a given table – each type of key can cover multiple columns. So, that is one feature that all the different types of keys share – they can each be comprised of more than just one column, which is something that many people in software are not aware of.
Of course, the database programmer is the one who will actually define which columns are covered by a foreign, primary, or unique key. That is one similarity all those keys share, but there are also some major differences that exist between primary, unique, and foreign keys. We will go over those differences in this article. But first, we want to give a thorough explanation of why foreign keys are necessary in some situations.

What is the point of having a foreign key?

Foreign keys are used to reference unique columns in another table. So, for example, a foreign key can be defined on one table A, and it can reference some unique column(s) in another table B. Why would you want a foreign key? Well, whenever it makes sense to have a relationship between columns in two different tables.

An example of when a foreign key is necessary

Suppose that we have an Employee table and an Employee Salary table. Also assume that every employee has a unique ID. The Employee table could be said to have the ‘master list’ of all Employee ID’s in the company. But, if we want to store employees salaries in another table, then do we want to recreate the entire master list of employee ID’s in the Employee Salary table as well? No – we don’t want to do that because it’s inefficient. It would make a lot more sense to just define a relationship between an Employee ID column in the Employee Salary table and the “master” Employee ID column in the Employee table – one where the Employee Salary table can just reference the employee ID in the Employee table. This way, whenever someone’s employee ID is updated in the Employee table, it will also automatically get updated in the Employee Salary table. Sounds good right? So now, nobody has to manually update the employee ID’s in the Employee Salary table every time the ID is update in the master list inside the Employee table. And, if an employee is removed from the Employee table, he/she will also automatically be removed (by the RDBMS) from the Employee Salary table – of course all of this behavior has to be defined by the database programmer, but hopefully you get the point.

Foreign keys and referential integrity

Foreign keys have a lot to do with the concept of referential integrity. What we discussed in the previous paragraph are some of the principles behind referential integrity. You can and should read a more in depth article on that concept here: Referential integrity explained.

Comments

Post a Comment

Popular posts from this blog

Prime Number Program in C#

Prime number is a number that is greater than 1 and divided by 1 or itself. In other words, prime numbers can't be divided by other numbers than itself or 1. For example 2, 3, 5, 7, 11, 13, 17, 19, 23.... are the prime numbers. using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace ConsoleApplication1 {     class Program     {         static void Main(string[] args)         {             int number, i, m = 0, flag = 0;             Console.WriteLine("PLEASE ENTER THE NUMBER TO CHECK PRIME NUMBER");             number = int.Parse(Console.ReadLine());             m = number / 2;             for(i=2;i<=m;i++)             {           ...
Read more

Fibonacci Series in C#

In the case of Fibonacci series, the next number is the sum of the previous two numbers for example 0, 1, 1, 2, 3, 5, 8, 13, 21 etc. The first two numbers of Fibonacci series are 0 and 1. using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace ConsoleApplication1 {     class Program     {         static void Main(string[] args)         {             int val1 = 0, val2 = 1, val3, i, number;             Console.WriteLine("Please Enter any number");             number = int.Parse(Console.ReadLine());             Console.WriteLine(val1 + " " + val2);             for (i=2;i<=number;i++)             {                 val3 =...
Read more

view in sql server

Image
A view is nothing more than a  saved SQL query . A view can also be considered as a virtual table .   A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table. SQL CREATE VIEW Syntax CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE coHEndition SQL Script to create tblEmployee table: CREATE TABLE  tblEmployee (  Id  int Primary Key ,  Name  nvarchar (30),  Salary  int ,  Gender  nvarchar (10),  DepartmentId  int ) When this query is executed , the database engine actually retrieves the data from the underlying base tables,  tblEmployees and tblDepartments . The View itself, doesnot store any data by default. When to Use a Vi...
Read more