SQL Keys

An introduction to Database

SQL Primary Key

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

In designing the composite primary key, you should use as few columns as possible. It is good for storage and performance both, the more columns you use for primary key the more storage space you require.


In terms of performance, less data means the database can process faster.

Points to remember for primary key:

  • Primary key enforces the entity integrity of the table.
  • Primary key always has unique data.
  • A primary key length cannot be exceeded than 900 bytes.
  • A primary key cannot have null value.
  • There can be no duplicate value for a primary key.
  • A table can contain only one primary key constraint.
  • When we specify a primary key constraint for a table, database engine automatically creates a unique index for the primary key column.

Main advantage of primary key
The main advantage of this uniqueness is that we get fast access.
In oracle, it is not allowed for a primary key to contain more than 32 columns.

SQL primary key for one column:

The following SQL command creates a PRIMARY KEY on the “S_Id” column when the “students” table is created.

MySQL:
CREATE TABLE students  
(  
S_Id int NOT NULL,  
LastName varchar (255) NOT NULL,  
FirstName varchar (255),  
Address varchar (255),  
City varchar (255),  
PRIMARY KEY (S_Id)  
)  

SQL primary key on ALTER TABLE

When table is already created and you want to create a PRIMARY KEY constraint on the id column you should use the following SQL:

Primary key on one column:

ALTER TABLE students  
ADD PRIMARY KEY (S_Id)  
Primary key on multiple column:
ALTER TABLE students  
ADD CONSTRAINT pk_StudentID PRIMARY KEY (S_Id,LastName)  

When you use ALTER TABLE statement to add a primary key, the primary key columns must not contain NULL values (when the table was first created).

How to DROP a PRIMARY KEY constraint?
If you want to DROP (remove) a primary key constraint, you should use following syntax:

ALTER TABLE students  
DROP PRIMARY KEY  
SQL Server / Oracle / MS Access:
ALTER TABLE students  
DROP CONSTRAINT pk_StudentID  

SQL FOREIGN KEY:

In the relational databases, a foreign key is a field or a column that is used to establish a link between two tables. In simple words you can say that, a foreign key in one table used to point primary key in another table.
Let us take an example to explain it:
Here are two tables first one is students table and second is orders table.
Here orders are given by students.

S_IdLastNameFirstNameCITY
1MAURYAAJEETALLAHABAD
2JAISWALRATANGHAZIABAD
3ARORASAUMYAMODINAGAR

Second table:

O_IdOrderNoS_Id
1995864652
2784665882
3223548463
4576986561

Here you see that “S_Id” column in the “Orders” table points to the “S_Id” column in “Students” table.

  • The “S_Id” column in the “Students” table is the PRIMARY KEY in the “Students” table.
  • The “S_Id” column in the “Orders” table is a FOREIGN KEY in the “Orders” table.

The foreign key constraint is generally prevents action that destroy links between tables. It also prevents invalid data to enter in foreign key column.

SQL 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 guaranteed, 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.

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 TABLE_NAME  
(COLUMN_1, DATA_TYPE_1,  
COLUMN_2, DATA_TYPE_2,  
???  
PRIMARY KEY (COLUMN_1, COLUMN_2, ?.));