{"id":186,"date":"2024-11-20T20:32:49","date_gmt":"2024-11-20T13:32:49","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=186"},"modified":"2025-01-22T09:50:14","modified_gmt":"2025-01-22T02:50:14","slug":"postgresql-primary-key","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-primary-key\/","title":{"rendered":"PostgreSQL Primary Key"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about primary keys and how to use the PostgreSQL primary key constraint to create a primary key for a table.<\/p>\n\n\n\n

Defining primary key #<\/a><\/h2>\n\n\n\n

A primary key is a column or a set of columns uniquely identifying each row in a table. It ensures that every row has a distinct value in the primary key columns.<\/p>\n\n\n\n

For example, the brand_id<\/code> column is the primary key of the following brands<\/code> table:<\/p>\n\n\n\n

brand_id<\/th>name<\/th><\/tr><\/thead>
1<\/td>Apple<\/td><\/tr>
2<\/td>Samsung<\/td><\/tr>
3<\/td>Google<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

In this brands table, the brand_id<\/code> 1 identifies the brand name Apple<\/code>, 2 indicates the Samsung<\/code> and 3 means Google<\/code>. There are no duplicate numbers in the brand_id<\/code> column.<\/p>\n\n\n\n

In PostgreSQL, you use the PRIMARY KEY<\/code> constraint to define a primary key for a table.<\/p>\n\n\n\n

If a primary key is a single column, you define the PRIMARY KEY<\/code> constraint as a column constraint by adding PRIMARY KEY<\/code> keywords after the primary key column:<\/p>\n\n\n

CREATE<\/span> TABLE<\/span> table_name<\/span>(\n   column1 data_type PRIMARY KEY<\/span>,\n\u00a0 \u00a0column2 data_type,\n\u00a0 \u00a0column3 data_type,\n\u00a0 \u00a0...\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, column1<\/code> is the primary key column of the table.<\/p>\n\n\n\n
When a primary key column has two or more columns, you can define the primary key as a table constraint:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> table_name<\/span>(\n\u00a0\u00a0\u00a0column1 data_type,\n\u00a0\u00a0\u00a0column2 data_type,\n \u00a0\u00a0column3 data_type,\n \u00a0\u00a0...\n\u00a0 \u00a0PRIMARY KEY<\/span> (column1, column2)\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the primary key includes column1<\/code> and column2<\/code>. In other words, no two rows will have the same values in column1<\/code> and column2<\/code>. When a primary key consists of two or more columns, it is called a composite primary key.<\/p>\n\n\n\n
Adding a primary key to a table #<\/a><\/h2>\n\n\n\n
If you have a table that does not have a primary key, you can add one using the following ALTER TABLE<\/code> statement:<\/p>\n\n\n
ALTER<\/span> TABLE<\/span> table_name<\/span>\nADD<\/span> PRIMARY KEY<\/span> (column1, column2, ...);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Auto-increment primary keys #<\/a><\/h2>\n\n\n\n
An auto-increment column, a popular choice for a primary key, due to its simplicity and efficiency. It automatically generates a unique number for each new row inserted into the table, eliminating the need for manual input and ensuring data uniqueness.<\/p>\n\n\n\n
To define an auto-increment column in PostgreSQL, you use the GENERATED ALWAYS AS IDENTITY<\/a><\/code> attribute as follows:<\/p>\n\n\n
id INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
To define an auto-increment column as a primary key column, you add the PRIMARY KEY<\/code> constraint:<\/p>\n\n\n
id INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Note the use of SERIAL<\/code> is less recommended due to permission and lack of integrity issues.<\/p>\n\n\n\n
Creating PostgreSQL primary key examples #<\/a><\/h2>\n\n\n\n
Let’s take some examples of defining primary keys.<\/p>\n\n\n\n
Defining a primary key column for a table #<\/a><\/h3>\n\n\n\n
The following example creates a table<\/a> called with the brand_id<\/code> as the primary key column:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> brands (\n  brand_id INT<\/span> GENERATED<\/span> ALWAYS<\/span> AS<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n  name<\/span> VARCHAR<\/span>(50<\/span>)\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Since the brand_id<\/code> column is auto-incremented, you don’t need to provide a value for that column.<\/p>\n\n\n\n
The following statement inserts<\/a> a new row into the brands<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  brands (name<\/span>)\nVALUES<\/span>\n  ('Apple'<\/span>)\nRETURNING<\/span>\n  *;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The statement inserts one row into the brands<\/code> table with the id 1.<\/p>\n\n\n
 brand_id | name<\/span>\n----------+-------<\/span>\n        1<\/span> | Apple<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you insert a new row, its id will be 2:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  brands (name<\/span>)\nVALUES<\/span>\n  ('Samsung'<\/span>)\nRETURNING<\/span>\n  *;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 brand_id |  name<\/span>\n----------+---------<\/span>\n        1<\/span> | Apple\n        2<\/span> | Samsung<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Creating composite primary key example #<\/a><\/h3>\n\n\n\n
A primary key may consist of two or more columns. For example, the following CREATE TABLE<\/a><\/code> statement creates a new table product_tags<\/code> whose primary key includes the product_id<\/code> and tag_id<\/code> columns:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> product_tags (\n  product_id INT<\/span>,\n  tag_id INT<\/span>,\n  PRIMARY KEY<\/span> (product_id, tag_id)\n);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n