Powered by Blogger.

DataBase Keys

,
Type of Dada Base Keys
  •         Primary Key
  •        Foreign key
  •        Composite Key
  •        Natural Key
  •        Surrogate Key
  •        Candidate Key
  •        Compound key
Primary Key:  Primary key must contain UNIQUE Values, and cannot contain Null Values.
Example:
CREATE TABLE TBLNAME (ID INT NOT NULL PRIMARY KEY,
                      FIRSTNAME VARCHAR (225) NOT NULL,
                      LASTNAME VARCHAR (225) NOT NULL);

+-----+---------------------+-------------+
| ID  | LASTNAME             |   LASTNAME   |
+-----+---------------------+-------------+
| 100 | RAJ KUMAR            |  VISHWAKARMA |
 | 101 | RAMESH KUMAR         |  VISHWAKARMA | 
+-----+---------------------+-------------+
Foreign key:  Foreign key is a collection of fields in one table that refers to the primary key in another table
Example:  Create a two Table as a TBL1 AND TBL2
1.       TBL1- ID1 is Primary Key in TBL1
+-----+---------------------+-------------+
| ID1 | LASTNAME1            |   LASTNAME1  |
+-----+---------------------+-------------+
| 100 | RAJ                  |  VISHWAKARMA |
 | 101 | RAJU                 |  VISHWAKARMA | 
+-----+---------------------+-------------+
2.       TBL2- ID1 is foreign key in TBL2 which is reference of column ID1 Primary key in TBL1.
+----+----------+--------+
| ID | NAME     |  ID1   |
+----+----------+--------+
|  1 | RAJU    |   101  |
|  2 | Raj     |   100  |
                                      +-----+----------+--------+

Composite Key:  Composite key is a key which is the combination of more than one field or column of a given table. It may be a candidate key or primary key.
Columns that make up the composite key can be of different data types.
CREATE TABLE TBLNAME (COLUMNNAME1, DATATYPE1,
                      COLUMNNAME2, DATATYPE2,
                      ???
                      PRIMARY KEY (COLUMNNAME1, COLUMNNAME1));

Natural Key: A Natural Key is a type of unique key or also known as Business Key.

Surrogate Key:  A Surrogate key is a made up value with the sole purpose of uniquely identifying a row. This is represented by an auto incrementing ID.
CREATE TABLE TBLNAME (SURROGATE_KEY INT IDENTTY (1, 1),
                      FIRSTNAME VARCHAR (225) NOT NULL,
                      LASTNAME VARCHAR (225) NOT NULL);

Candidate key: A Candidate Key is a column or set of column in a database table.
+----+----------+-----------+
| ID | NAME     |  IDCODE   |
+----+----------+-----------+
|  1 | RAJU    |   101     |
|  2 | Raj     |   100     |
                                      +----+----------+-----------+
A Primary Key is being selected from the group of candidate keys. That means we can either have ID and IDCODE as Primary keys.

Compound Key:  A Compound Key is a key that consists of two or more attributes that uniquely identify an entity Occurrence.