What is a primary key?
Primary Key is a combination of column(s) which act as an identifier of each row for a table. It helps in understanding the granularity of a data. Suppose you have a data for each patient, on which date he/she visited the hospital and purpose of visit, our possible combination of primary could be patient_id,date_of_visit and purpose_of_visit.
A primary key must satisfy following two constraints:
NOT NULL: Columns which make up together a primary key can never have NULL values.
UNIQUE: Combination of columns must combine together to form a unique values.
An example:
For eg. Suppose 3 columns form our primary key: patient_id,date_of_visit and purpose_of_visit.
patient_id can have duplicate values, similarly date_of_visit and purpose_of_visit columns can have duplicate values, but when these 3 columns are concatenated together to form a single column then that newly created column must have unique values.
Creating a Primary key from scratch:
Syntax:
CREATE TABLE table_name(
column_name_1 data_type constraint,
column_name_2 data_type constraint, column_name_3 data_type constraint, ... column_name_n data_type constraint); Adding a constraint in CREATE TABLE are optional. They can later on be added using ALTER TABLE.
CREATE TABLE mytable2(
CustName varchar(100) PRIMARY KEY,
Gender char(1),
Age numeric,
City varchar(100),
Salary numeric);Alternatively, you can define PRIMARY KEY CONSTRAINT at the end of the CREATE TABLE as follows: Syntax:
CREATE TABLE mytable(
col1 datatype1,
col2 datatype2,
...
col n datatypeN,
PRIMARY KEY (list of columns for primary key));
CREATE TABLE mytable3(
CustName varchar(100),
Gender char(1),
Age numeric,
City varchar(100),
Salary numeric,
PRIMARY KEY (CustName));Primary key does not allow duplicate and NULL values:
If you try to insert duplicate values or NULL values in CustName column in table3 then an error will be encountered:
INSERT INTO mytable3(CustName,Gender)
VALUES ('Ronald','M');
INSERT INTO mytable3(Gender)
VALUES ('M');
INSERT INTO mytable3(CustName,Gender)
VALUES ('John','M');
INSERT INTO mytable3(CustName,Gender)
VALUES ('Ronald','M');First query will successfully get executed, but second query will throw up an error : because it will lead to NULL values in CustName column,
Similarly third query will not lead to any issues but fourth query will lead to errors: because of duplicate value in CustName (as Ronald is already there in first row).
Creating a Primary key using ALTER TABLE:
Suppose you want to define the primary key in an already created dataset then you can use ALTER TABLE to add the constraints.By default while creating a dataset every column has NULL constraint. Thus before applying the PRIMARY KEY constraint using ALTER TABLE, firstly we need to apply the NOT NULL constraint first and then the PRIMARY KEY constraint.
Task: Add a PRIMARY KEY constraint to columns patient_id and department in the table patient_info.
ALTER TABLE patient_info
ALTER COLUMN patient_id int NOT NULL;
ALTER TABLE patient_info
ALTER COLUMN department varchar(128) NOT NULL;
ALTER TABLE patient_info
ADD PRIMARY KEY(patient_id,department);Note: If you already have NULL values or duplicate values in the dataset then you are likely to encounter errors in applying UNIQUE or NOT NULL constraints.
How to deal with this? Firstly, remove the NULL values by either deleting the rows or replacing them using UPDATE statement, and tackle the duplicate rows.