SocialSecurityNumber
|
Name
|
Address
|
BirthDay
|
TaxIDNumber
|
818764259
|
John
|
22 washington st
|
1/3/1972
|
837650987
|
517881756
|
Raj
|
1 boston road
|
6/9/1964
|
767354174
|
788876566
|
Janet
|
23 avenue NYC
|
4/15/1985
|
939635252
|
748209944
|
Janet
|
23 avenue NYC
|
4/15/1980
|
643228844
|
Uniquely identifying a row means being able to pin point to one row. For example suppose I need you to delete the fourth row, if I tell you, delete the row where the name is Janet. There are two such rows, so that means I cannot uniquely identify a row (or pin point a row) with the name alone.
But if I tell you delete the row where Name is Janet , address is 23 avenue NYC and birth day is 4/15/1980. Then you will delete the correct row. That means a combination of Name,Address and Birthday, uniquely identifies a row.
(You can also uniquely identify a row with SocialSecurityNumber or TaxIDNumber )
(You can also uniquely identify a row with SocialSecurityNumber or TaxIDNumber )
Super key: A combination of columns that can uniquely identify
a row in a table. A super key can have more columns than it needs to uniquely
identify a row.
For example in the
above table [SocialSecurityNumber+
Name+Birthday] is a super key. Note here that SocialSecurityNumber
by itself can uniquely identify a row. Name and Birthday are extra columns that are not required to uniquely
identify a row.
Candidate key: A minimal super key. (A sub set of super key, where if you
remove even one column, you cannot identify a row uniquely any more)
Assume that a particular
address you cannot have two
individuals with the same name AND date of birth.
In the example above SocialSecurityNumber, TaxIDNumber
and the combination [Name+Addresss+ Birthday] are all Candidate keys. But if you remove Birthday from [Name+Addresss+
Birthday] then it is not a candidate key anymore.
Primary key: The main candidate key in a table. (The
main column or combination of columns that uniquely identifies a row). In the
above example, SocialSecurityNumber
is the primary key.
Note that when you set a
column as a primary key in SQL server, it also automatically sets that column
(or the combination of columns) as a “clustered index”. But if you want, you can
remove the clustered index on the primary key. It is not mandatory to have the
primary key as the clustered index.
Unique Key: Unique is practically (but not theoretically) the same as superkey. Unique key is a constraint that you put on a table where you want to ensure that the value is not repeated between two rows.
For example, in my SQL database table I want to make sure that no two rows have the same TaxIDNumber. To do that in SQL Server, I will right click on the table-->Design
In the new window that opens up, I will right click the table-->Indexes/Keys. Click Add. Then make the selections as shown in the image below.
Note here that you can set a unique key on a combination of columns. You can also set multiple unique keys in a table.
Difference between primary and unique key:
Difference between primary and unique key:
Primary key constraint
|
Unique key constraint
|
A table can have only one primary
key.
|
A table can have multiple unique
keys.
|
Doesn’t allow null in any of the
columns that are a part of the primary key.
|
Allows nulls in all the columns
that are a part of the unique key constraint.
|
By default a clustered index is
created on the column(s) that you set the primary key on.
(This index can be dropped later,
it is not mandatory)
|
By default a non clustered index
is created on the column(s) that you set the Unique key on.
(This index can be dropped later,
it is not mandatory)
|
Composite key (Compound key): A key that includes more than one column.
Foreign key constraint: This is more easily explained with an example. Consider the tables shown below.
The table Students hold the roll numbers for each student. The grade table holds a grade for each roll number. To make sure a grade for a roll number should not exist in the Grade table, if that roll number is not present in the students table, we will set a foreign key as shown below. With a foreign key set, sql server will not allow the entry of a roll number in the Grades table, unless that roll number exists in the Students table.
Note here that rollNumber has to be defined as a primary or uniqe key in the students table for this foreign key to be possible.
Further Reading:
Database Normalization :http://dotnetanalysis.blogspot.com/2012/01/database-normalization-sql-server.html
Foreign key constraint: This is more easily explained with an example. Consider the tables shown below.
The table Students hold the roll numbers for each student. The grade table holds a grade for each roll number. To make sure a grade for a roll number should not exist in the Grade table, if that roll number is not present in the students table, we will set a foreign key as shown below. With a foreign key set, sql server will not allow the entry of a roll number in the Grades table, unless that roll number exists in the Students table.
Note here that rollNumber has to be defined as a primary or uniqe key in the students table for this foreign key to be possible.
Further Reading:
Database Normalization :http://dotnetanalysis.blogspot.com/2012/01/database-normalization-sql-server.html
please give one example with sql query .so that we can understand easily.
ReplyDeleteThanks for this great explanation!
ReplyDeletewhat about the difference between candidate and composite?
ReplyDeleteCandidate key can be single column, composite key on the other hand has to have at least 2 columns.
Delete