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
Share your thoughts in the comments
Please Login to comment...