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.

Answer 1

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?