Open In App
Related Articles

PostgreSQL – Primary Key

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report
In this article we will look into the PostgreSQL Primary key constraints using SQL statements. A primary key is a column or a group of columns used to identify a row uniquely in a table. Technically speaking a primary key constraint is the blend of a not-null constraint and a UNIQUE constraint. Only one primary key must exist in a table. PostgreSQL produces a unique B-tree index on the column or a group of columns used that defines the primary key whenever a primary key is added to the table.
Syntax: column_name Data-type PRIMARY KEY
Now let’s look into some examples. Example 1: In this example we will create a table (say, books) and add a column(say, book_id) as the primary key of the table.
CREATE TABLE books (
    book_id INTEGER PRIMARY KEY,
    title TEXT,
    price INTEGER
);
Now let’s insert some data to our table using the below command:
INSERT INTO books(book_id, title, price)
VALUES
    ('101', 'Jobs', '2000'),
    ('102', 'Geeta', '250'),
    ('103', 'Ramayana', '354'),
    ('104', 'Vedas', '268');
Now we will check if the inserted works as intended using the below command:
 SELECT * FROM books;
Output: Example 2: In this example we will be adding PRIMARY key to an existing table. So, lets first create one using the command below:
CREATE TABLE vendors (name VARCHAR(255));
Now add few rows to the vendors table using INSERT statement as below:
INSERT INTO vendors (NAME)
VALUES
    ('Microsoft'),
    ('IBM'),
    ('Apple'),
    ('Samsung');
Now verify the insert operation, we query data from the vendors table using the following SELECT statement:
SELECT
    *
FROM
    vendors;
This will give us the below result: Now, if we want to add a primary key named id into the vendors table and the id field is auto-incremented by a single unit, we use the below statement:
ALTER TABLE vendors ADD COLUMN ID SERIAL PRIMARY KEY;
Let’s check the vendors table again using the below command:
SELECT
    id, name
FROM
    vendors;
Output:

Last Updated : 01 Jun, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads