Primary key for multiple column in PostgreSQL?
PostgresqlDatabase DesignPrimary KeyDdlComposite Primary-KeyPostgresql Problem Overview
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.
Postgresql Solutions
Solution 1 - Postgresql
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:
Solution 2 - Postgresql
I think it can be.
Create table "Test"
(
"SlNo" int ,
"EmpID" int ,
"Empname" text),
"EmpAddress" text,
PRIMARY KEY (SlNo, EmpID)
);
Solution 3 - Postgresql
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/