Primary key for multiple column in PostgreSQL?

Question

How to provide primary key for multiple column in a single table using PostgreSQL?

Example:

Create table "Test" 
(
   "SlNo" int not null primary key,
   "EmpID" int not null, /* Want to become primary key */
   "Empname" varchar(50) null,
   "EmpAddress" varchar(50) null
);

Note: I want to make "EmpID" also a primary key.

I'm not sure I quite understand, are you trying to create a single primary key with both columns, or two entirely separate keys? — Joachim Isaksson, May 08 '14 at 05:11

Erwin Brandstetter · Accepted Answer · 2021-06-08T02:22:25.807

There can only be one primary key per table - as indicated by the word "primary".
You can have additional UNIQUE columns like:

CREATE TABLE test(
   sl_no int PRIMARY KEY,  -- NOT NULL due to PK
   emp_id int UNIQUE NOT NULL,
   emp_name text,
   emp_addr text
);

Columns that are (part of) the PRIMARY KEY are marked NOT NULL automatically.

Or use a table constraint instead of a column constraint to create a single multicolumn primary key. This is semantically different from the above: Now, only the combination of both columns must be unique, each column can hold duplicates on its own.

CREATE TABLE test(
   sl_no int,     -- NOT NULL due to PK below
   emp_id int ,   -- NOT NULL due to PK below
   emp_name text,
   emp_addr text,
   PRIMARY KEY (sl_no, emp_id)
);

Multicolumn UNIQUE constraints are possible, too.

Aside: Don't use CaMeL-case identifiers in Postgres. Use legal, lower-case identifiers so you never have to use double-quotes. Makes your life easier. See:

Are PostgreSQL column names case-sensitive?

Very useful tip for newcomers regarding CaMeL-case also - thanks :-) — omatai, Jun 08 '21 at 02:22

score 0 · Answer 2 · answered May 17 '21 at 06:30

0

I think it can be.

Create table "Test" 
(
   "SlNo" int ,
   "EmpID" int , 
   "Empname" text),
   "EmpAddress" text,
   PRIMARY KEY (SlNo, EmpID)
);

answered May 17 '21 at 06:30

Hong Van Vit

2,672
2
13
37

How does this add anything useful? – Erwin Brandstetter Feb 10 '22 at 04:09
Using the quotes because of Pascal casing every time seems like a hassle. Also, watch those parentheses. – General Grievance Feb 10 '22 at 14:04

score 0 · Answer 3 · answered Feb 23 '22 at 19:53

In case you want to specify the name of the primary key constraint:

CREATE TABLE test(
   sl_no int not null,
   emp_id int not null,
   emp_name text,
   emp_addr text,
   constraint pk_test primary key (sl_no, emp_id)
);

Source: https://www.postgresqltutorial.com/postgresql-primary-key/

Primary key for multiple column in PostgreSQL?

3 Answers3

Linked