Primary key for multiple columns 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.

Answer 1

There can only be one PRIMARY KEY constraint per table (spanning one or more columns) - as indicated by the word "primary".
You can have additional UNIQUE constraints (spanning one or more columns).

Short syntax with "column constraints":

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

Columns that are (part of) the PRIMARY KEY are marked NOT NULL automatically. Some like to add a NOT NULL constraint explicitly. Redundant, but doesn't hurt.
UNIQUE constraints allow null values - unless the column is marked NOT NULL explicitly. See:

• Create unique constraint with null columns

The same can be achieved with "table constraints" - the only syntax option to involve multiple columns. Like a multicolumn PRIMARY KEY. Now, only the combination of columns must be unique, each column can hold duplicates on its own. Minimal syntax:

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

Or full syntax with custom constraint name:

...
, CONSTRAINT test_pkey PRIMARY KEY (sl_no, emp_id)
...

"test_pkey" also happens to be the default PK name for a table named "test".

There are a number of optional additional modifiers. Read the manual.

Aside: Use legal, lower-case identifiers in Postgres, so you never have to double-quote. See:

• Are PostgreSQL column names case-sensitive?