How to define Primary Key in POSTGRESQL

 Primary Key is a key how data physically aligned in the memory system. It will give ability for fast retrieval of data.

Key aspects in primary key

1. When you define a primary key PostgreSQL automatically creates a unique B-tree index on the columns available in the table.
2. Use SERIAL or BIGSERIAL for auto-incrementing primary keys. These types automatically generate unique sequential values. refer (available data types in POSTGRESQL)
3. Each table can have only one primary key
4. but a primary key can consist of multiple columns (composite key).

Example for creating table

CREATE TABLE Wings
         ( 
             WingID uuid primary key, 
             WingName VARCHAR(100), 
             IsActive boolean 
        );

Alter the existing table:

ALTER TABLE Wings ADD COLUMN ColID SERIAL PRIMARY KEY;

SQL PRIMARY KEY Constraint

 The PRIMARY KEY constraint uniquely identifies each record in a database table.
Primary keys must contain unique values.
A primary key column cannot contain NULL values.
Each table should have a primary key, and each table can have only ONE primary key.
SQL PRIMARY KEY Constraint on CREATE TABLE:
The following SQL creates a PRIMARY KEY on the "P_Id" column when the "Persons" table is created:
SQL Server / Oracle / MS Access:                                                                                   CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
MySQL / SQL Server / Oracle / MS Access:                                                                  CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)
MySQL:                                                                                                                              CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)
SQL PRIMARY KEY Constraint on ALTER TABLE
MySQL / SQL Server / Oracle / MS Access:                                                                      ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id)
To DROP a PRIMARY KEY Constraint
MySQL / SQL Server / Oracle / MS Access:                                                                            ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID