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.
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:
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:
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/
A multi-column primary key can be specified after all columns:
CREATE TABLE test(
sl_no int,
emp_id int,
emp_name text,
emp_addr text,
PRIMARY KEY (sl_no, emp_id)
);
Source: https://www.postgresql.org/docs/current/sql-createtable.html
