articles

Home / DeveloperSection / Articles / SQL KEYS IN RDBMS

SQL KEYS IN RDBMS

Anupam Mishra 5235 01-Feb-2016
 PRIMARY KEY (PK):

A column or columns is called primary key (PK) that uniquely identifies each row in table. If we want to create a primary key, we should defines a PRIMARY KEY constraints when we create or modify a table. When we are working with multiple columns are used as a primary key, it is known as a composite primary key.

In creation time of composite primary key, we should use a few column as possible. It is good for storage and performance both, the more columns we use for primary key the more storage space you require.

If we are using primary key in my tables that must be follow a following
condition:

1.       It enforces the entity integrity of the table.

2.       It always has unique data

3.       Key length can’t exceeded than 900 bytes.

4.       Key can‘t have a null value.

5.       There can be no duplicate value for a primary key.

6.       A table can contain only one primary key constraints.

In internally, when we are creating a primary key constraints for a table, database engine has create automatically a unique index for the primary key. The main advantage of creating a primary key is that we get fast access with uniqueness.

Syntax of Creating a Primary key in Single Column:
Table creation time:
CREATE TABLE Employee
(
Employee_Id int NOT NULL PRIMARY KEY,
Employee_Full_Name varchar (255)   NOT NULL,
Employee_Address varchar (255),
Employee_City varchar (255)
)

After Table Creation:  If we are not defining a primary key in creation time of table .So, we will define a primary key constraints after table creation, using following syntax:

ALTER TABLE Employee
ADD PRIMARY KEY (Employee_Full_Name)
 Syntax of Creating a Primary key in Multiple Column:

Table creation time:

CREATE TABLE Employee
(
Employee_Id int NOT NULL,
Employee_Full_Name varchar (255) NOT NULL, Employee_Address varchar (255), Employee_City varchar (255) CONSTRAINTS pk_EmployeeID PRIMARY KEY (Employee_Id, Employee_Full_Name) )

If we want to drop a primary key constraints in my tables then we will use following syntax:

ALTER TABLE Employee
DROP CONSTRAINTS pk_EmployeeID
  FOREIGN KEY:

In RDBMS, a foreign key is a field or a column i.e. used to establish a link between two tables. Or we say simply, a foreign key in one table are using a primary key in another table.

Now, we are explain with example. Here, we have two tables of Employee as follows:

Employee_ID

FirstName

LastName

City

001

ANUPAM

MISHRA

DELHI

002

ANKUSH

SONI

MURADABAD

003

AKHILESH

ANAND

LUCKNOW

004

AMRISH

SHAH

KANPUR

 Department Table:

Employee_Dep_ID

Department

Employee_ID

1001

Computer Science

1

1002

Electrical

2

1003

Civil

1

 

Here “Employee_ID” column is the “Employee” table is the primary key in the “Employee” table. The “Employee_ID” column in “Department” table is a foreign key in Department table.

We have also define a foreign key creation time of the table “Department”(we have taken as above)

CREATE TABLE Department
(
Emp_Dep_ID int NOT NULL PRIMARY KEY,
Department varchar (255) NOT NULL,
FOREIGN  KEY  (Employee_ID)
REFERENCES Employee(Employee_ID)
)

 We have also define a foreign key constraints after the table creation. So, we are using following constraints:

ALTER TABLE Department
ADD CONSTRAINTS fk_Departments
FOREIGN KEY (Employee_ID) REFERENCES Employee (Employee_ID)

 Drop Syntax for FOREIGN KEY CONSTRAINTS:

Alter table Department
DROP CONSTRAINTS fk_Departments
Here some important differences between primary key and foreign key in sql.

1.    Foreign key is a null but primary key is not.

2.   Foreign key can be duplicate but primary key is not.

3.   By default primary key adds a clustered index but a foreign key does not automatically create an index, clustered or non-clustered. We must manually create an index for foreign key.

4.   There is an only one primary key in a table but we can have more than one foreign key.

 Composite Key:

A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table when the columns are combined uniqueness is guarantee, but when it taken individually it does not guarantee uniqueness.

Sometimes more than one attributes are needed to uniquely identify an entity.  A primary key that is made by the combination of more than one attribute is known as a composite key.

In other words we can say that:

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.

Syntax:
CREATE TABLE table_name
(
Column1 datatype,
Column2 datatype,
??????
PRIMARY KEY (Column1, Column2)
)

Here, all cases composite key created consist of column1 and column2. 

UNIQUE KEY:

We can say it is a little like a primary key. If we want to define uniqueness a single field/column or more than one in database table, we using a UNIQUE KEY .We have defines more than one unique keys in a table but only one primary key.

For example we are taking a above “Employee” table and defines a UNIQUE Key instead of Primary key.

CREATE TABLE Employee
(
Employee_ID int NOT NULL UNIQUE
Employee_Full_Name varchar(255),
Employee_City varchar(255)
)

ALTERNATE KEY:

If we are defining more than one candidate key in a table and rest columns there are one is primary key and other is called an alternate key. We can also say, an alternate key, the column may not be primary key but still it is a unique key in the column. For example, we are taken an “Employee” table but Employee_ID is a primary key and rest one is a candidate key.

 

 

 


Leave Comment

Comments

Liked By